Page 1 of 1

Like Function in DB2 cursor

PostPosted: Wed Sep 26, 2012 5:49 pm
by vkumarg3
Hi,

I want to use a LIKE statement in the DB2 Cursor query. Again in the Select query i am coding the LIKE statement using a host variable...

Declare c1 CURSOR for
select col1, col2, col3
where col1 = :col1-var
and col2 like :col2-var

..i am concatenating the value in col2-var as
string %, temp-var and % in to col2-var...so my col2-var will look like
'%user-value%'

When the fetch operation is performed, i am always getting a return code of 100.

Please help me out, if there are any specific thing to be followed while using LIKE function in cursors....also i tried hard coding the like values in the cursor...then it was fetching properly..only if i use the host variable , i am getting a sqlcode of 100..

Thanks in advance.

Re: Like Function in DB2 cursor

PostPosted: Wed Sep 26, 2012 6:03 pm
by GuyC
how did you define col2-var ?
probably this host variable contains '%uservalue% ' with trailing spaces, causing the like to not find any rows.
try string '%' temp-var '%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%' into col2-var

Re: Like Function in DB2 cursor

PostPosted: Wed Sep 26, 2012 6:08 pm
by vkumarg3
I have declared col2-var and temp-var like this.

01 col2-var pic x(04)
01 temp-var pic x(02).

I am moving 2 byte charracter to temp-char correctly. So there is no chance of trailing spaces.

Re: Like Function in DB2 cursor

PostPosted: Wed Sep 26, 2012 8:04 pm
by dick scherrer
Hello,

Suggest you try what GuyC has provided. . . If it does Not do what you want, further investigation might be needed, but if it works, it means you have not quite correctly understood why the 100 is raised.