Can we use a COBOL group variable in an SQL "IN" c

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

I have a COBOL group variable like
01 VARS
05 VAR1
05 VAR2
05 VAR3
05 VAR4

Is it possible to use
SELECT field
FROM table
WHERE
field2 IN (:VARS)

instead of
SELECT field
FROM table
WHERE
field2 IN (:VAR1,:VAR2,:VAR3,:VAR4)?

guptae · Posted: Wed Sep 27, 2006 12:33 pm

Hi Yoyoyo,

Do u know the mean of both the query is totally different.
suppose this variables contain following value
01 VARS
05 VAR1 11
05 VAR2 22
05 VAR3 33
05 VAR4 44

Then ur first query will select the rows from table for which field equals to 11223344 while in the case of second query it will select all the rows for which field2 value is 11 or 22 or 33 or 44.

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

Hi Ekta,

Thanks for your reply!

But what if the field 'field2' is CHAR(2)

Will DB2 recognize it as a list of 2-character values

Cheers!

Y

guptae · Posted: Wed Sep 27, 2006 2:45 pm

Hi Yoyoyo,

No db2 wont recognize it.

UmeySan · Posted: Wed Sep 27, 2006 2:50 pm

Hi !

No, as Ekta told you, the values in the fields don't matter.
It's the Select-Comand.
In first example the row is exactly compared with the value of :VARS in
the whole length of VARS.

Regards, UmeySan

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

Hi

It worked!!!
I tried coding in both the ways. Both are giving the same result.
Any explanations?

YoYoYo

hikaps14 · Active User Joined: 02 Sep 2005 Posts: 189 Location: Noida

Hi ,

I guess Dynamic SQL will help u.

what u can use is

01 VARS
05 VAR1
05 filler pic x value ','
05 VAR2
05 filler pic x value ','
05 VAR3
05 filler pic x value ','
05 VAR4

now ur querry can work but u can't directly execute ur query.
u will have to first prepare ur querry n then execute.
actually i m not quite familiar with dynamic SQl
may be some one else could give u exact process n syntax.

Thanks,
-Kapil

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

Hi hikaps14,

I don't think we need to use any dynamic SQLs for this. We don't need to give ',' in FILLERs also.

This means that if I give
IN (:VARS)
or
IN (:VAR1, :VAR2, :VAR3, :VAR4)
in the COBOL-DB2 program, the result will be the same.

Yoyoyo!

hikaps14 · Active User Joined: 02 Sep 2005 Posts: 189 Location: Noida

Hi Yoyoyo,

u r right .
but wat i was suggesting ,
if u just want to code a single group level variable in the where Clause.

Thanks,
-Kapil.

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

Hi Kapil,

I'm already coding a single group level variable (namely VARS) in the WHERE clause. But your suggestion was to include the FILLERs ',' which I think is not required!

Thanks a lot!

Yoyoyo!

priyesh.agrawal · Posted: Mon Oct 16, 2006 1:46 am

Yoyoyo,

shreevamsi · Posted: Mon Oct 16, 2006 10:56 am

Hi Yoyoyo,

You will get a compilation error if you code a group element inyour EXEC SQL.

You need to redefine the group element and code the query.

~Vamsi

surya_pathaus · Active User Joined: 28 Aug 2006 Posts: 110

Hi Yoyoyo,

As Ekta told, 1st query will search for the whole string "11223344" where in second query searches for "11 or 22 or 33 or 44".

You may get correct results with both queries. But you test with the above data which is given by Ekta then you may get different results.

As priyesh asked, what is the field2 pic clause. Is it x(2) or x(8). What is the Pic clause of VAR1,VAR2,VAR3,VAR4.

If field2 is of x(2) then 1st query will return sqlcode 100.

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

surya_pathaus · Active User Joined: 28 Aug 2006 Posts: 110

Yoyoyo,

Did you tested your code with the above data?
Post your test data.

I think when only VAR1 contain value i,e., all other fields VAR2,VAR3,VAR4 contain spaces then 2 queries will return same result. In othercases it is not possible to return same result.

guptae · Posted: Tue Oct 31, 2006 1:34 pm

Hi Yoyoyo ,

I am also agree with Surya.U will get the same result only when var2,var3,var4 is spaces.

ovreddy · Posted: Tue Oct 31, 2006 3:01 pm

Hi Ektha,

You are rite. Both the STATEMENTS will do the same. The first query is a best method to use in IN clause. This is a regular practice for us.

For your information we cannot use array in IN clause. Also there is a limit in the number of elements in IN clause.

Thanks,
Reddy

guptae · Posted: Tue Oct 31, 2006 3:07 pm

Hi Overreddy,

Its Ekta not Ektha...

& We are not using array in IN clause. He mentioned group level variable there in first query.

dbzTHEdinosauer · Posted: Tue Oct 31, 2006 10:38 pm

01 WS-RELT-TYPES.
05 WS-RELT-TYPE-01 PIC X(4).
05 WS-RELT-TYPE-02 PIC X(4).
05 WS-RELT-TYPE-03 PIC X(4).
05 WS-RELT-TYPE-04 PIC X(4).
05 WS-RELT-TYPE-05 PIC X(4).
05 WS-RELT-TYPE-06 PIC X(4).
05 WS-RELT-TYPE-07 PIC X(4).
05 WS-RELT-TYPE-08 PIC X(4).
05 WS-RELT-TYPE-09 PIC X(4).
05 WS-RELT-TYPE-10 PIC X(4).

READ RELATION-TYPES-IN INTO WS-RELT-TYPES

EXEC SQL
SELECT RELT_TYPE_I
, RELT_LOC_I
INTO :RELT-TYPE-I
, :RELT-LOC-I
FROM table_name
WHERE RELT_TYPE_I
IN (:WS-RELT-TYPES)
AND LOC_I
= :LOC-I
END-EXEC

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

Hi surya_pathaus,

The code is working perfectly for me.

Regarding test data:
I will get the Char(2) codes '01','02','03' and '04' into the variables VAR1, VAR2, VAR3 and VAR4 respectively.

So, instead of using IN (:VAR1,:VAR2,:VAR3,:VAR4),
I will be using IN (:VARS) in the SELECT query.
& this works selecting the records for the codes '01','02','03' and '04'!!!!!

Let me know if anyone needs further clarification.

YoYoYo

ovreddy · Posted: Wed Nov 01, 2006 6:27 pm

Hi,

Both the queries are same. We can very well use a group variable as parameter for IN. It is equalant of using invidual variables.

Thanks,
Reddy

Yoyoyo · New User Joined: 19 Sep 2006 Posts: 26

surya_pathaus,

The code is working fine for me.

01 VARS
05 VAR1 - '01'
05 VAR2 - '02'
05 VAR3 - '03'
05 VAR4 - '04'

When I use the query,