|
View previous topic :: View next topic
|
| Author |
Message |
Rao Vivek
New User
Joined: 13 Jan 2015
Posts: 2
Location: India
|
|
|
|
Hi,
I am currently working on a VSAM to DB2 migration project. I have a question about how to handle OCCURS clause in DB2. Below are some details about what I am doing.
| Code: |
Data in VSAM file:
|-----|-----|-----|
|--|--|--|--|--|--|--|--|--|--|
1234001002003011012011013011014
1235004005006012015012016012017
01 WS-RECORD.
05 WS-REC-KEY PIC 9(4).
05 WS-FIELD-1 PIC 9(3).
05 WS-FIELD-2 PIC 9(3).
05 WS-FIELD-3 PIC 9(3).
05 WS-FIELD-OCC OCCURS 3 TIMES
10 WS-FIELD-4 PIC 9(3).
10 WS-FIELD-5 PIC 9(3).
DB2 Tables:
MASTER TABLE (Primary Key: WS-REC-KEY)
|-----------|------------|------------|------------|
|WS-REC-KEY | WS-FIELD-1 | WS-FIELD-2 | WS-FIELD-3 |
|-----------|------------|------------|------------|
| 1234 | 001 | 002 | 003 |
|-----------|------------|------------|------------|
| 1235 | 004 | 005 | 006 |
|-----------|------------|------------|------------|
OCCURS TABLE (Primary Key: SEQ NO + WS-REC-KEY, Foreign Key: WS-REC-KEY)
|-------|------------|------------|------------|
|SEQ NO | WS-REC-KEY | WS-FIELD-4 | WS-FIELD-5 |
|-------|------------|------------|------------|
| 01 | 1234 | 011 | 012 |
|-------|------------|------------|------------|
| 02 | 1234 | 011 | 013 |
|-------|------------|------------|------------|
| 03 | 1234 | 011 | 014 |
|-------|------------|------------|------------|
| 01 | 1235 | 012 | 015 |
|-------|------------|------------|------------|
| 02 | 1235 | 012 | 016 |
|-------|------------|------------|------------|
| 03 | 1235 | 012 | 017 |
|-------|------------|------------|------------| |
The question I have is how do I get data from the Master and Occurs table in the same format as that of a VSAM file record. Basically, is there a way to get multiple rows into a single record format in DB2?
I have tried using JOIN but that gives me a result set which I would then have to read using a CURSOR and apply logic within the program specifically to get the data into a single record format.
Let me know if you need further information on this.
Please Help !!! |
|
| Back to top |
|
 |
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
You cannot, as far as I know, escape using a cursor. That is what it is there for: processing multiple rows that meete the condition.. So, you will need to process the cursor for each occurrence in the 'occurs' table.
If you held the number of occurrences in the 'master' table you could retrieve all the rows in one FETCH. |
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
| I am not sure if this works but just a thought, Try using CASE stmts and convert these rows of same WS-REC-KEY to multiple columns of WS-FIELD-4 and WS-FIELD-5 and then put a join between these two tables. |
|
| Back to top |
|
|
Rao Vivek
New User
Joined: 13 Jan 2015
Posts: 2
Location: India
|
|
|
|
Thank you for your responses. Sounds like I dont have much if a choice than to use a Fetch loop.  |
|
| Back to top |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2455
Location: Hampshire, UK
|
|
|
|
| It's what loops are for. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| Code: |
select MASTER.REC_KEY , FIELD1 , FIELD2 , FIELD3
,max(case when SEQNO = 1 then FIELD4 end) as FIELD4_1
,max(case when SEQNO = 1 then FIELD5 end) as FIELD5_1
,max(case when SEQNO = 2 then FIELD4 end) as FIELD4_2
,max(case when SEQNO = 2 then FIELD5 end) as FIELD5_2
,max(case when SEQNO = 3 then FIELD4 end) as FIELD4_3
,max(case when SEQNO = 3 then FIELD5 end) as FIELD5_3
from MASTER join OCCURSTB on MASTER.REC_KEY = OCCURSTB.REC_KEY
group by MASTER.REC_KEY , FIELD1 , FIELD2 , FIELD3 |
|
|
| Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010
Posts: 3051
Location: NYC,USA
|
|
|
|
@GuyC: You read my mind....  |
|
| Back to top |
|
|
|
|
