Issue with Like in Embeded SQL



IBM's flagship relational database management system

Issue with Like in Embeded SQL

Postby nikesh_rai » Mon Jul 13, 2015 4:22 pm

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
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Issue with Like in Embeded SQL

 

Re: Issue with Like in Embeded SQL

Postby alexm » Mon Jul 13, 2015 7:07 pm

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...
User avatar
alexm
 
Posts: 33
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Issue with Like in Embeded SQL

Postby nikesh_rai » Mon Jul 13, 2015 10:35 pm

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
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Issue with Like in Embeded SQL

Postby pranav283 » Mon Jul 20, 2015 12:27 pm

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?
pranav283
 
Posts: 35
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Issue with Like in Embeded SQL

Postby nikesh_rai » Mon Jul 20, 2015 1:19 pm

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)
Thanks
Nikesh Rai
nikesh_rai
 
Posts: 192
Joined: Tue Oct 18, 2011 1:27 am
Has thanked: 17 times
Been thanked: 0 time

Re: Issue with Like in Embeded SQL

Postby pranav283 » Mon Jul 20, 2015 4:28 pm

Great that you figured it out !
pranav283
 
Posts: 35
Joined: Sat Aug 30, 2014 3:52 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post