Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Can we use a COBOL group variable in an SQL "IN" c
Goto page 1, 2  Next
 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Sep 27, 2006 11:20 am    Post subject: Can we use a COBOL group variable in an SQL "IN" c
Reply with quote

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)?
Back to top
View user's profile Send private message

guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Sep 27, 2006 12:33 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Sep 27, 2006 2:30 pm    Post subject:
Reply with quote

Hi Ekta,

Thanks for your reply!

But what if the field 'field2' is CHAR(2) icon_question.gif
Will DB2 recognize it as a list of 2-character values icon_question.gif

Cheers! icon_biggrin.gif
Y icon_wink.gif Y icon_wink.gif Y icon_wink.gif
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Wed Sep 27, 2006 2:45 pm    Post subject:
Reply with quote

Hi Yoyoyo,

No db2 wont recognize it.
Back to top
View user's profile Send private message
UmeySan

Active Member


Joined: 22 Aug 2006
Posts: 743
Location: Germany

PostPosted: Wed Sep 27, 2006 2:50 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Sep 27, 2006 3:40 pm    Post subject:
Reply with quote

Hi

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

YoYoYo
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Sep 27, 2006 3:41 pm    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

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
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Sep 27, 2006 4:14 pm    Post subject:
Reply with quote

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!
Back to top
View user's profile Send private message
hikaps14

Active User


Joined: 02 Sep 2005
Posts: 189
Location: Noida

PostPosted: Wed Sep 27, 2006 4:33 pm    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

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.
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Sep 27, 2006 4:47 pm    Post subject:
Reply with quote

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! icon_biggrin.gif

Thanks a lot!

Yoyoyo!
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Mon Oct 16, 2006 1:46 am    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

Yoyoyo,

Quote:
I tried coding in both the ways. Both are giving the same result.
Any explanations?

It might be working; as Field2 would be taking partial values from VARS. Like only VAR1.
For a clear picture, could you put pic definition for FIELD2 and VARS.
Back to top
View user's profile Send private message
shreevamsi

Active User


Joined: 23 Feb 2006
Posts: 305
Location: Hyderabad,India

PostPosted: Mon Oct 16, 2006 10:56 am    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Mon Oct 16, 2006 12:16 pm    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

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.
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Tue Oct 31, 2006 12:29 pm    Post subject:
Reply with quote

Code:

01 VARS.
   05 VAR1   PIC X(2).
   05 VAR2   PIC X(2).
   05 VAR3   PIC X(2).
   05 VAR4   PIC X(2).



field2
is a CHAR(2) field.

Shreevamsi - I didnt get any compilation error and my code is working fine.
Back to top
View user's profile Send private message
surya_pathaus

Active User


Joined: 28 Aug 2006
Posts: 110

PostPosted: Tue Oct 31, 2006 12:56 pm    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

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.
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Oct 31, 2006 1:34 pm    Post subject:
Reply with quote

Hi Yoyoyo ,

I am also agree with Surya.U will get the same result only when var2,var3,var4 is spaces.
Back to top
View user's profile Send private message
ovreddy

Active User


Joined: 06 Dec 2004
Posts: 211
Location: Keane Inc., Minneapolis USA.

PostPosted: Tue Oct 31, 2006 3:01 pm    Post subject:
Reply with quote

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
Back to top
View user's profile Send private message
guptae

Moderator


Joined: 14 Oct 2005
Posts: 1187
Location: Bangalore,India

PostPosted: Tue Oct 31, 2006 3:07 pm    Post subject:
Reply with quote

Hi Overreddy,

Its Ekta not Ektha... icon_sad.gif

& We are not using array in IN clause. He mentioned group level variable there in first query.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Tue Oct 31, 2006 10:38 pm    Post subject: Re: Can we use a COBOL group variable in an SQL "IN&quo
Reply with quote

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
Back to top
View user's profile Send private message
Yoyoyo

New User


Joined: 19 Sep 2006
Posts: 26

PostPosted: Wed Nov 01, 2006 2:57 pm    Post subject:
Reply with quote

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
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
Goto page 1, 2  Next
Page 1 of 2

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. RANDOM Function in COBOL swapnil781 COBOL Programming 2 Tue Nov 15, 2016 6:17 pm
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts COBOL Version 6.1 Virendra Shambharkar COBOL Programming 5 Tue Nov 01, 2016 11:24 am
No new posts Cobol list of programs being called biswajit.dattagupta COBOL Programming 5 Tue Nov 01, 2016 2:10 am
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us