Page 1 of 1
Issue with Like in Embeded SQL
Posted:
Mon Jul 13, 2015 4:22 pm
by nikesh_rai
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.
AND A.Axxx_Ixxx_Cxx = :Gxxxxx-Axxx-Ixxx-Cxx
AND A.Uxxx_Axxx_Nx
LIKE (:Gxxxxx-Uxxx-Axxx-Nxx||'%')
AND A.Ixxxx_Rxxxx_Nxx = :Gxxxxx-Ixxxx-Rxxxx-Nxx
AND B.Mxxx_Pxxx_Cxx = 'Z'
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
Re: Issue with Like in Embeded SQL
Posted:
Mon Jul 13, 2015 7:07 pm
by alexm
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...
Re: Issue with Like in Embeded SQL
Posted:
Mon Jul 13, 2015 10:35 pm
by nikesh_rai
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
Re: Issue with Like in Embeded SQL
Posted:
Mon Jul 20, 2015 12:27 pm
by pranav283
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?
Re: Issue with Like in Embeded SQL
Posted:
Mon Jul 20, 2015 1:19 pm
by nikesh_rai
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.
05 WS-FLTR-CUST-ID PIC X(13) VALUE '%%%%%%%%%%%%%'.
SQL Predicate:
AND PARM.ACCT_CTRY_CDE = :XXXXX-ACCT-CTRY-CDE
AND PARM.UFMT_ACCT_NUM LIKE :WS-FLTR-CUST-ID
AND PARM.INSTR_REFER_NUM = :XXXXX-INSTR-REFER-NUM
String formatting
MOVE xxxxxx-FLTR-CUST-ID TO WS-CUST-ID-STRG
PERFORM VARYING WS-INDEX FROM 1 BY 1
UNTIL WS-EOS-YES
OR WS-INDEX > 12
IF WS-STRING(WS-INDEX) NOT = SPACES
ADD 1 TO WS-STR-CNT
ELSE
SET WS-EOS-YES TO TRUE
END-IF
END-PERFORM
MOVE WS-CUST-ID-STRG TO WS-FLTR-CUST-ID(1:WS-STR-CNT)
Re: Issue with Like in Embeded SQL
Posted:
Mon Jul 20, 2015 4:28 pm
by pranav283
Great that you figured it out !