IBM Mainframe Forum

Hi,

I have coded an online program to fetch records from tables. I have used Like in predicates of the SQL, however the SQL is fetching the data only when exact match is found and not fetching other rows.



Say when I am passing Nikesh1 to Gxxxxx-Uxxx-Axxx-Nxx, it is fetching the exact value, but if passing only Nikesh.. then it is not fetching all the rows whose column Uxxx_Axxx_Nxx is starting with Nikesh.

Can you please suggest

Hi Nikesh,

What's the declaration of the host variable 'Gxxxxx-Uxxx-Axxx-Nxx' in your program? What's the corresponding datatype and length of the DB2 column?
What's the programming language you're working with, and how do you move the values into the host variable?

If the host variable is defined with, e.g., PIC X(20) and in Cobol you move 'Nikesh' (6 bytes) to it, then you will get 14 trailing spaces, and this concatenated with '%' sign on position 21...

Thanks Alex,

Yes, I declared the host variable as PIC X(35). I think I got the point you want to say. I need to handle '%' where string will end exactly. As per my current logic, even if my string have lenght of 6 char, '%' is being placed at 36th position, and causing the issue

Hi Nikesh,

Are you not using a cursor ?
Because, without the use of it you won't get multiple rows. Remember !
Using only host-variables will fetch you just one row, and any further attempt to retrieve 'matching' rows will suffocate the host varaibles and your program is likely to get abend with SQLCODE -811.

So you can check that whether your program abend or not, and if not, then why ?
Apart from what alexm has suggested, there may not be any further matching rows !
If possible, can you pelase post the full query so that we can have a better look?

Thanks Pranav,

I am using cursor for the embedded SQL. Hence not getting any issue.. and now it is working fine, after handling '%' properly in my program. Now, if the string is of 8 chars, I am placing the '%' sign at 9th position.



SQL Predicate:



String formatting

Great that you figured it out !