Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Like in EXEC SQL statement

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Tue Jul 08, 2008 10:30 pm    Post subject: Like in EXEC SQL statement
Reply with quote

Hi,

Please let me know if we can use like operand in EXEC SQL or not?

If yes, please let me know the solution for this below requirement...

My query must retrieve data from a table when a field C contains the string '123' anywhere in it.

i used the below code for doing that.

move 123 to L1.
string '%','123','%' into L2

exec sql

Select a,b,c from table1 where c like L2
end-exec

but the query is not retrieving any data.

Please let me know if the above code is correct or suggest any other way.
Back to top
View user's profile Send private message

Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Jul 09, 2008 12:02 am    Post subject:
Reply with quote

Yes. We can. Please try this.

Declare a Working Storage variable as given below

01 L2.
05 L2-1 PIC X(01).
05 L2-2 PIC X(03).
05 L2-3 PIC X(XX) (remaining length of column C).

Move '%' to L2-1
Move all '%' to L2-3
Move '123' to L2-2

Exec SQL
Select a,b,c from table1 where c like :L2
End-Exec
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6968
Location: porcelain throne

PostPosted: Wed Jul 09, 2008 11:14 am    Post subject:
Reply with quote

good post Suresh.
Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Wed Jul 09, 2008 11:17 am    Post subject:
Reply with quote

Check your L2 variable. Confirm whether your column "C" in table contains "123". And You can use "CONCAT" verb in EXEC SQL STATEMENT.
Ex: CONCAT('%', string)
Back to top
View user's profile Send private message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Wed Jul 09, 2008 11:48 am    Post subject: Reply to: Like in EXEC SQL statement
Reply with quote

Hi Suresh,

thanks for the reply. but it is not working..

I am getting this below error in pre-compilation itself.

DSNH132I E DSNHSM2P LINE 1562 COL 22 AN OPERAND OF "LIKE" IS NOT VALID

By the way the column C is of varchar data type.

I also declared a group variable under which there is a length item of the text ('%123%') and one more field for the actual text (%123%)...like below..


PostPosted: Wed Jul 09, 2008 12:02 am Post subject:
Yes. We can. Please try this.

Declare a Working Storage variable as given below

01 L2.
05 L2-len pic s9(04) comp.
05 L2-text.
10 L2-1 PIC X(01).
10 L2-2 PIC X(03).
10 L2-3 PIC X(XX) (remaining length of column C).

Move 5 to L2-len

Move '%' to L2-1
Move all '%' to L2-3
Move '123' to L2-2

and I gave this group variable in the LIKE operation.even then it did not work.

Then it is giving this error in precompilation..besides the above stated error.

DSNH312I E DSNHSMUD LINE 1562 COL 44 UNDEFINED OR UNUSABLE HOST VARIABLE
Back to top
View user's profile Send private message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Wed Jul 09, 2008 12:29 pm    Post subject:
Reply with quote

Hi birdy,

I have used CONCAT in my LIKE operand.
If I hard code value 123 in L2, I am able to retrieve some rows from database.

But If I move the actual value that is coming to this program( ie. 123 or 456 or 2312 etc - this is 5 char length), I am not getting any data.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 09, 2008 3:48 pm    Post subject:
Reply with quote

Hari your incoming variable is 5 char length ... so if you string use

string '%',L2,'%' the output will be '%123 %' ... the trailing spaces are not getting removed ...

In you where clause for LIKE use this ...

Code:


WHERE C LIKE CONCAT(CONCAT('%',RTRIM(:L2)),'%')



this will remove the trailing spaces if at all present and concat % at the beginning and end ...

try and let us know ...
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 09, 2008 4:31 pm    Post subject:
Reply with quote

You can even use

Code:


'%'||RTRIM(:L2)||'%'

Back to top
View user's profile Send private message
birdy K

New User


Joined: 05 Mar 2008
Posts: 72
Location: chennai

PostPosted: Wed Jul 09, 2008 5:33 pm    Post subject:
Reply with quote

I faced the same problem. As that column is varchar , look at the DCLLIB. There will names for length and Text . So move the length to that table length variable and text to table text variable

Suppose in your case "C" is column name. As it is a varchar, in DCLLIB there will be

01 C
05 CLEN PIC S9(04) COMP.
05 CTEXT PIC LENGTH

So move the max length to CLEN and text(%123%) to CTEXT. And use this query. I am sure there will no error.

Exec SQL
Select a,b,c from table1 where c like :tablename.c
End-Exec


Corrections are welcome.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Wed Jul 09, 2008 5:45 pm    Post subject:
Reply with quote

Quote:

Declare a Working Storage variable as given below

01 L2.
05 L2-1 PIC X(01).
05 L2-2 PIC X(03).
05 L2-3 PIC X(XX) (remaining length of column C).



I guess you cannot give a structure in LIKE stmts ....
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Jul 09, 2008 7:35 pm    Post subject:
Reply with quote

Hi hariibm

If it is a VARCHAR field then you need to give the below given structure..

Declare a Working Storage variable as given below

01 L2.
05 L2-1 PIC X(01).
05 L2-2 PIC X(03).
05 L2-3 PIC X(XX) (remaining length of column C).


Declare another working storage variable of the Size same as column C.

01 L3 PIC X(XX) (length of column C)

Move L2 to L3

Exec SQL
Select a,b,c from table1 where c like :L3
End-Exec

And also please note that you do not need to use the DCLGEN Variable always. You can use a COBOL Working storage variable with the same datatype as DCLGEN variable.

Hi Ashimer,

Yes. That is true. You cannot use L2 directly in the Select statement. We need to move this to an another variable of the same length and can use in the program.
Back to top
View user's profile Send private message
trilokdewangan

New User


Joined: 19 Jun 2008
Posts: 39
Location: USA

PostPosted: Tue Jul 15, 2008 3:13 pm    Post subject:
Reply with quote

Hi ,

Above code given by Suresh will work. you can also use REDEFINES key word.

Code:
01 L2.
    05 L2-1 PIC X(01).
    05 L2-2 PIC X(03).
    05 L2-3 PIC X(01).
    05 L3 REDEFINES L2 PIC X(05).

Move '%' to L2-1
Move all '%' to L2-3
Move '123' to L2-2


use the code below for EXEC SQL statement:

Code:
Exec SQL
Select a,b,c from table1 where c like :L3
End-Exec


Thanks,
Trilok Kumar
Back to top
View user's profile Send private message
hariibm

New User


Joined: 05 Feb 2007
Posts: 61
Location: Chennai

PostPosted: Wed Jul 16, 2008 10:52 am    Post subject: Reply to: Like in EXEC SQL statement
Reply with quote

Hi,
Thanks everybody.
The combination of CONCAT and RTRIM has worked for me as suggested by ashimer.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts WER268A OUTREC STATEMENT : SYNTAX E... frozenblood87 SYNCSORT 12 Sat Aug 26, 2017 9:45 pm
No new posts Suppress value reported by BREAK in D... Learncoholic DFSORT/ICETOOL 3 Wed Aug 16, 2017 6:03 pm
No new posts ON 2 AND EVERY 1 - Statement ??? UmeySan COBOL Programming 2 Tue Jul 25, 2017 1:20 pm
No new posts Running an REXX EXEC implicitly sho7hills CLIST & REXX 3 Thu Apr 20, 2017 8:14 am
No new posts ROUNDED Problem with COMPUTE statement shalem COBOL Programming 11 Thu Feb 09, 2017 8:16 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us