My cursor's Select query has the following like clause in its WHERE clause, but the procedure failed to compile, because of this like clause.

here the colid of both the table are of type varchar(2)

But DB2 allows only string constants and not a column value in the pattern match of a like clause.
(ie. the above query works fine if the WHERE clause is, table1.colid like 'aa')

could any one suggest me on how to use the column value here in the pattern match of the like clause..

Regards
Sn

Try LOCATE() or SUBSTR() functions

i dint find SUBSTRING() in DB2 .. and i think LOCATE will not replace the table column as the exact equivalent.. kindly explain me with an example if i am wrong.

thanks
Sn

might be something like this works:

SELECT table1.colid, col3
FROM table1, table3

WHERE SUBSTR(table3.colid,1,1) in (SUBSTR(table1.colid,1,1),'_','%')
AND SUBSTR(table3.colid,2,1) in (SUBSTR(table1.colid,2,1),'_','%')

ORDER BY table1.colid;

I tried your suggestion with SUBSTR umayer, but the query fails in execution throwing the following exeception :

No authorized routine named 'SUBSTR' of type 'FUNCTION' having compatible arguments is found.
SQL State : 42884


Pls note that my DB 2 version is 8.1.3 and it runs on Windows 2000

thanks
Sn

If you just want to match the two COLID columns, and both are VARCHAR(2), what's wrong with(or am I misunderstanding your problem)?