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
 

 

A single query needed for the below requirement

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

New User


Joined: 17 Feb 2007
Posts: 38
Location: Hyderabad

PostPosted: Wed Sep 12, 2007 11:48 pm    Post subject: A single query needed for the below requirement
Reply with quote

Hi All,
I have to create a single query for the below sample requirement to make our data setups simple. We do this kind of data setups very frequently and hence looking for a single query to do the setups.

We have the below input:

State
AP
MH
MP
HP
KA
TN

Language
TL
TM
KA
HI
ML

Bus segment
CN
BS


Using the above input, we need to insert into a table in the below format

RULE_CD SEQ_NBR QUALF_CD QUALF_VAL
1 1 ST AP
1 2 LG TL
1 3 BS CN
2 1 ST AP
2 2 LG TL
2 3 BS BS
3 1 ST AP
3 2 LG TM
3 3 BS CN
4 1 ST AP
4 2 LG TM
4 3 BS BS
5 1 ST AP
5 2 LG KA
5 3 BS CN
6 1 ST AP
6 2 LG KA
6 3 BS BS
and so on.....
this way we will get a total of 180 records (6 * 5 * 2 * 3).
If you observe, we need to create all possible combinations from the input and insert into the table.
Each possible combination should have same RULE_CD and SEQ_NBR should be unique with in a RULE_CD.

For QUALF_CD,
ST refers to state
LG refers to Language
BS refers to BS

Where as QUALF_VAL will have the actual value corresponding to QUALF_CD.

Can somebody help me out in this. It need not be a single query. It can be a series of queries which takes the above input in one shot. We just want to reduce the manual effort.

Please let me know if you need more explanation.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Sep 13, 2007 12:31 am    Post subject:
Reply with quote

Hello,

What is/are the source(s) of the input?

I believe you could read the input(s) into one or more cobol array(s) and "spin" thru them building the rows as you need them. As each set of values is built, insert that row into the table.
Back to top
View user's profile Send private message
TG Murphy

Active User


Joined: 23 Mar 2007
Posts: 149
Location: Ottawa Canada

PostPosted: Thu Sep 13, 2007 12:44 am    Post subject:
Reply with quote

I don't think you can do this using SQL queries. The problem is generating your rule code numbers and sequence numbers.

Dick has described the best solution. I was going to suggest REXX/DB2 because it supports recursion - but few people know REXX - and even fewer know the REXX/DB2 interface so support becomes an issue.

COBOL is your best bet...
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 Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts IMS DB-How to update a record (a sing... Nic Clouston IMS DB/DC 9 Thu Mar 09, 2017 4:38 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm


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