IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

A single query needed for the below requirement


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
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
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

Moderator Emeritus


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

PostPosted: Thu Sep 13, 2007 12:31 am
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: 148
Location: Ottawa Canada

PostPosted: Thu Sep 13, 2007 12:44 am
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts How to append a PS file into multiple... JCL & VSAM 3
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
Search our Forums:

Back to Top