IBM MAINFRAME HELP & SUPPORT FORUMS

hariibm · Joined: 05 Feb 2007 Posts: 44 Location: Chennai

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.

Suresh Ponnusamy · Joined: 22 Feb 2008 Posts: 92 Location: New York

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

dbzTHEdinosauer · Joined: 20 Oct 2006 Posts: 1618 Location: germany

good post Suresh.

birdy K · Joined: 05 Mar 2008 Posts: 68 Location: chennai

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)

hariibm · Joined: 05 Feb 2007 Posts: 44 Location: Chennai

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

hariibm · Joined: 05 Feb 2007 Posts: 44 Location: Chennai

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.

ashimer · Joined: 13 Feb 2004 Posts: 354 Location: Bangalore

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 ...

ashimer · Joined: 13 Feb 2004 Posts: 354 Location: Bangalore

You can even use

Code:

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

birdy K · Joined: 05 Mar 2008 Posts: 68 Location: chennai

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.

ashimer · Joined: 13 Feb 2004 Posts: 354 Location: Bangalore

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 ....

Suresh Ponnusamy · Joined: 22 Feb 2008 Posts: 92 Location: New York

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.

trilokdewangan · Joined: 19 Jun 2008 Posts: 20 Location: Bangalore

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

hariibm · Joined: 05 Feb 2007 Posts: 44 Location: Chennai

Hi,
Thanks everybody.
The combination of CONCAT and RTRIM has worked for me as suggested by ashimer.