LIKE Predicate problem

Robert Drinovac

unread,

Jan 15, 2002, 6:09:46 PM1/15/02

to

I am currently having a problem with the LIKE predicate. I have included a
simplified statement of what I am trying to do below:

select changedate from ingrmaster where changedate like changedate

this results in error:

SQL0132N A LIKE predicate or POSSTR scalar function is not valid because
the first operand is not a string expression or the second operand is not a
string. SQLSTATE=42824

State:42824,Native:-132,Origin:[IBM][CLI Driver][DB2/6000]

The changedate is a char column. This statement works in other database
like Oracle. If I change the above statement to this:

select changedate from ingrmaster where changedate like '20020115'

this works without returning an error.

Is there anyway I can make the first statement work with DB2?

--
Thanks in advance to anyone who responds.

Robert Drinovac

Dave Brown

unread,

Jan 15, 2002, 8:51:41 PM1/15/02

to

Like is only valid for 'string' data or a portion thereof.

....like 'abcd12%'
....like '%cd12'
....like '%cd%'

the % symbol denotes any value that might precede or follow the stated
characters.

If you value (as in your example) is actually '20020115' and you said ....
like '20020115' (without a % ) then it would satisfy the condition. but if
you said like '200201' (without a %) you'd get nothing.

you can also use '_' (underscore) as a place holder, so '_bcd%' would return
any string containing 'bcd' in the third through fifth position.

Dave
"Robert Drinovac" <rdri...@invatron.com> wrote in message
news:lG218.88$EI....@tor-nn1.netcom.ca...

paul....@ukk.ibmm.comm

unread,

Jan 16, 2002, 4:05:33 AM1/16/02

to

> select changedate from ingrmaster where changedate like changedate
>this results in error:
>SQL0132N A LIKE predicate or POSSTR scalar function is not valid because
> the first operand is not a string expression or the second operand is
not a
> string. SQLSTATE=42824

use = instead of LIKE ??

but seriously as the error message says the second operand must be a
(literal) string rather than a column or a variable, which can be a real
pain at times.

What SQL really needs is full regular expression capability. Anybody else
agree?

Regards
Paul Vernon

sql-db2-dba

unread,

Jan 16, 2002, 10:30:02 AM1/16/02

to

I am assuming you have a static query where the 2nd reference of
changedate is a parameter. You need to put ':' in front of the
parameter ...

select changedate from ingrmaster where changedate like :changedate

"Robert Drinovac" <rdri...@invatron.com> wrote in message news:<lG218.88$EI....@tor-nn1.netcom.ca>...

Aakash Bordia

unread,

Jan 16, 2002, 1:27:02 PM1/16/02

to

I have tried this earlier and faced the same problem. The RHS argument to
LIKE cannot be a column name and I think this is a current limitation. You
can write a UDF for the time being to have a workaround.
so your statement would be something like:
select changedate from ingrmaster where LIKEUDF(changedate,changedate)
Thanks
Aakash

"Robert Drinovac" <rdri...@invatron.com> wrote in message
news:lG218.88$EI....@tor-nn1.netcom.ca...

Knut Stolze

unread,

Jan 16, 2002, 4:35:11 PM1/16/02

to

On Wed, 16 Jan 2002 09:05:33 +0000, paul....@ukk.ibmm.comm wrote:

> What SQL really needs is full regular expression capability. Anybody else
> agree?

Me! ;-)

Seriously, I missed regular expressions a few times. If you want to do
that, you can always write your own function today. So it's not that you
can't do it at all, it is just not that convenient.

--
Knut Stolze
DB2 Spatial Extender
IBM Silicon Valley Lab

Serge Rielau

unread,

Jan 16, 2002, 6:13:50 PM1/16/02

to

I think you can snicker a general LIKE function from the Sybase MTK (very likely
it's written in java)

Cheers
Serge
--
Serge Rielau
DB2 UDB SQL Compiler Development
IBM Software Lab, Canada

paul....@ukk.ibmm.comm

unread,

Jan 18, 2002, 2:46:51 PM1/18/02

to

Nice thinking Serge. Defiantly a useful source of UDFs. Know of any
others?

Are there any docs on the full usage of these functions? Some I can
guess:

--------------------------- Command entered ----------------------------
values SUBSTR(MS7.reverse('this is a string'),1,18)
------------------------------------------------------------------------

1
------------------
gnirts a si siht

--------------------------- Command entered ----------------------------
values MS7.islike(' 110% enough?','%110!% eno%','!')
------------------------------------------------------------------------

1
-----------
1

But others are more difficult,...

--------------------------- Command entered ----------------------------
values MS7.WAITFORFUNC('2', 'D')
------------------------------------------------------------------------
values MS7.WAITFORFUNC('2', 'D')

1
-----------
SQL4302N Java stored procedure or user-defined function
"MS7.WAITFORFUNC",
specific name "SQL020117152224698" aborted with an exception "Invalid
waitfor
options". SQLSTATE=38501

Why don't folks just use COMMENT ON ? (and that goes for the system tables
too ;-)

Regards
Paul Vernon

Serge Rielau

unread,

Jan 18, 2002, 5:33:54 PM1/18/02

to

Yes, the upcoming MTK for another DBMS ;-)
Hi Paul,

I have been harassing the respective manager for a while now to simply publish
the library.
Also I have been hoping that some participants in this newsgroup open-source
their handy funcs
(or sell them for good money to DB2 DD).

patrick Dantressangle

unread,

Jan 18, 2002, 7:43:34 PM1/18/02

to

Hi, Paul.
MTK is for sure a good source of UDFs that simulate Sybase & MS SQLServer
Built-in functions.(more to come for other RDBMS)
These UDF can be used by you or anybody else for free. You don't need the
source code for that as suggested Serge.

You have some explanation of the UDFs behavior in the MTK documentation under
the "Converter Reference: Sybase&MSSQLServer" page, "compatibility library
functions" and "conversion functions" pages.
We explain ( "very quickly" I must admit), what is the equivalent MTK UDF when
coming from Sybase or MS.

By using the MTK TSQL convertor (in MTK menu --> tools) you can actually see
how we translate the Sybase UDFs. This will help you understand what to
expect. Input the TSQL in the top text box, click convert, and you get the DB2
equivalent code using the MTK UDF in the bottom text box.
Try with a valid Sybase WAITFOR statement for instance.

You can also take a look at the MTKSyb.udf & MTKMS.udf file for more details
about the built-in functions.There is some comments in there.

For the future, we will try to share them somewhere on the web.But the initial
goal was not to share them stanalone, but to be part of the MTK conversion
process. To be shared standalone, we need to do more work on the files and
docs, something we cannot do right now.
But we are thinking of it. Be patient.

Regards
Patrick

patrick Dantressangle

unread,

Jan 18, 2002, 7:43:59 PM1/18/02

to

Regards
Patrick

patrick Dantressangle

unread,

Jan 18, 2002, 7:44:05 PM1/18/02

to

Regards
Patrick

patrick Dantressangle

unread,

Jan 18, 2002, 7:45:26 PM1/18/02

to

Regards
Patrick

patrick Dantressangle

unread,

Jan 18, 2002, 7:45:47 PM1/18/02

to

Regards
Patrick

patrick Dantressangle

unread,

Jan 18, 2002, 7:46:03 PM1/18/02

to

Regards
Patrick