LIKE function in DB2 Cursor

vikramr_83 · New User Joined: 27 Jun 2007 Posts: 4 Location: Hyderabad

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..

sai_sridar_s · New User Joined: 14 Nov 2006 Posts: 5

hi,
i read it from reference book...
If the pattern specified in a LIKE predicate is a parameter marker, and a fixed-length character host variable is used to replace the parameter marker, the value specified for the host variable must have the correct length. If the correct length is not specified, the select operation will not return the intended results.
For example, if the host variable is defined as CHAR(10), and the value WYSE% is assigned to that host variable, the host variable is padded with blanks on assignment. The pattern used is:

'WYSE% 'The database manager searches for all values that start with WYSE and that end with five blank spaces. If you want to search only for values that start with 'WYSE', assign a value of 'WSYE%%%%%%' to the host variable.

Just try this..

Sai Sridar S
Always Be Happy!

murugan_mf · Posted: Thu Jan 29, 2009 11:39 pm

Try Like This.