View previous topic :: View next topic
|
Author |
Message |
deepak.vl
New User
Joined: 17 Feb 2007 Posts: 38 Location: Hyderabad
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
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 |
|
|
TG Murphy
Active User
Joined: 23 Mar 2007 Posts: 148 Location: Ottawa Canada
|
|
|
|
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 |
|
|
|