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
 

 

Pass list values for a column using Cobol to Dynamic SQL

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 28, 2012 10:53 pm    Post subject: Pass list values for a column using Cobol to Dynamic SQL
Reply with quote

Hi,

My requirement is as below.

I need to select FST_NM column from NAME01 table for all the employees that are in EMPLOYEE table, and also for the 3 employees that are going to be derived dynamically. So I have repeated the column 4 times on total in the below dynamic sql.

Please suggest if any one of you done using some best approach other than this.

Code:
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
     OR  EMP_ID = ?
     OR  EMP_ID = ?
     OR  EMP_ID = ?


Other Approach I've tried:
I tried using IN clause as below.

Code:
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
     OR  EMP_ID [b]IN[/b] ?


For this case I've used STRING statement of COBOL to combine the 3 employees and passed to dynamic sql to replace the '?'. But I am getting -302 since the variable I am passing has PIC clause larger than the EMP_ID column. For example EMP_ID in the table is char(10). I have tried using X(38) to concatenate the 3 employees and building quotes and commas.

Please suggest me if there is any best method than the first method.

Regards
Amar
Back to top
View user's profile Send private message

V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 28, 2012 10:54 pm    Post subject:
Reply with quote

The code in second part of the post has a typo. Here is the code.

Quote:
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
OR EMP_ID IN ?
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jun 28, 2012 11:13 pm    Post subject:
Reply with quote

there are values for emp_id of NAME01
that are not in EMPLOYEE?
Back to top
View user's profile Send private message
agkshirsagar

Active Member


Joined: 27 Feb 2007
Posts: 685
Location: Earth

PostPosted: Thu Jun 28, 2012 11:19 pm    Post subject:
Reply with quote

Hi Amarendra,
From your first post, I assume you have a COBOL program that uses DYNAMIC SQL to get the desired output.
Can you please let us know why, this can't be achieved using good old EMBEDDED SQL cursor?
If you are concerned about the performance, embedded SQL is the way to go.

For example, the above query can be written-

Code:
SELECT FST_NM FROM NAME01
WHERE EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE)
     OR  EMP_ID = :WS-EMP-ID1
     OR  EMP_ID = :WS-EMP-ID2
     OR  EMP_ID = :WS-EMP-ID3
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 28, 2012 11:19 pm    Post subject: Reply to: Pass list values for a column using Cobol to Dynam
Reply with quote

Hi,

Yes there are for some employees in NAME01 that are not in EMPLOYEE.

Regards
Amar
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 28, 2012 11:22 pm    Post subject:
Reply with quote

Why did you choose Dynamic SQL?

Whats the minimum number of value you pass and maximum?
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 28, 2012 11:22 pm    Post subject:
Reply with quote

Hi agkshirsagar,

The table name needs to be dynamically determined. You see the table name I've given in the query was NAME01. It is actually NAMEXX where XX will be dynamically determined. Here XX ranges from 01-15. So if I need to do and embedded SQL then I need to have 15 DECLARE cursors/opens/Fetch/close. So our shop uses the dynamic SQL as the choice.

Regards
Amar
Back to top
View user's profile Send private message
V S Amarendra Reddy

Active User


Joined: 13 Sep 2006
Posts: 189
Location: INDIA

PostPosted: Thu Jun 28, 2012 11:25 pm    Post subject:
Reply with quote

Hi Pandora-Box,


Quote:
Whats the minimum number of value you pass and maximum?


Nice Question. For now it is 3 Employees, but some times they all might not present and sometimes they all are present. I am wondering what if there are going to be more than 3 in future. For now, can you please suggest for the 3 case. Since this is a new program as of now only 3 are considered.

Regards
Amar
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


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

PostPosted: Thu Jun 28, 2012 11:32 pm    Post subject:
Reply with quote

Quote:
So our shop uses the dynamic SQL as the choice.


even though, apparently, no one knows how to code? (write the proper sql)

the idea of dynamic sql is to allow for multiple options for
select lists
where clauses - thus possibly taking advantage of run-time optimization.


when the only difference is the name of 1 table,
15 or 500 cursors, does not matter, should be imbedded,
thus reducing your run-time bind processing.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1529
Location: Andromeda Galaxy

PostPosted: Thu Jun 28, 2012 11:32 pm    Post subject:
Reply with quote

So you are saying you wanted to process 01 - 15 tables and process one program Dynamically?

Also if you just wanted to select why not unload the needed data parallely and process ?

Note:Edited
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
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 SQZ pipe delimited file with 1 column... Rick Silvers DFSORT/ICETOOL 2 Sun Nov 06, 2016 8:11 pm
No new posts Overlaying one set of charater values... Kevin Lindsley SYNCSORT 7 Sat Nov 05, 2016 3:21 am
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


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