Page 1 of 1

Issue with Like in Embeded SQL

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: 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

PostPosted: Mon Jul 20, 2015 4:28 pm
by pranav283
Great that you figured it out !