Like Function in DB2 cursor



IBM's flagship relational database management system

Like Function in DB2 cursor

Postby vkumarg3 » Wed Sep 26, 2012 5:49 pm

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.
vkumarg3
 
Posts: 29
Joined: Fri Jan 06, 2012 9:29 am
Has thanked: 10 times
Been thanked: 0 time

Re: Like Function in DB2 cursor

Postby GuyC » Wed Sep 26, 2012 6:03 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Like Function in DB2 cursor

Postby vkumarg3 » Wed Sep 26, 2012 6:08 pm

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.
vkumarg3
 
Posts: 29
Joined: Fri Jan 06, 2012 9:29 am
Has thanked: 10 times
Been thanked: 0 time

Re: Like Function in DB2 cursor

Postby dick scherrer » Wed Sep 26, 2012 8:04 pm

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.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post