I have two tables , table A and table B.
Table b is the child of table a. I need to unload the data
Of both the tables and put it in a file.
Say the data in the tables are like this
TABLE A
ID(PK) NAME SEMESTER
001 THOMAS THIRD
002 SMITH FOURTH
003 JOHN FIRST
TABLE B
ID(PK) SEQ(PK) SUBJECTS
001 1 MATHS
001 2 GEOGRAPHY
001 3 PSYCHOLOGY
002 1 HISTORY
003 1 ENGLISH
003 2 ELECTRONICS
OUTPUT FILE
A.ID A.NAME A.SEMESTER B.ID B.SEQ B.SUBJECTS
001 THOMAS THIRD 001 1 MATHS
001 THOMAS THIRD 001 2 GEOGRAPHY
001 THOMAS THIRD 001 3 PSYCHOLOGY
002 SMITH FOURTH 002 1 HISTORY
003 JOHN FIRST 003 1 ENGLISH
003 JOHN FIRST 003 2 ELECTRONICS
The output should be such that when there are multiple TABLE B entries then one record would be created for each TABLE B with the TABLE A repeating at the beginning or each record.
I do an unload of the data from these two tables and keep them in two files. Then while merging, is it possible to obtain the above results using sort?
Both the files are FB and the rec length for file A is 38.
and for file B is 35
and the key for table A is ID which is defined as DECIMAL(18,0) in the database
and the key for table B are ID and seq number which are defined as DECIMAL(18,0) and SMALLINT
I will be unloading the data from table A to file A which is of length 38 and from table B to a file B which is of 35
and the the first 18 characters of each file will be the key X(18). I hope my answer is clear.
Joined: 07 Dec 2007 Posts: 2205 Location: San Jose
roshnii wrote:
I will be unloading the data from table A to file A which is of length 38 and from table B to a file B which is of 35
and the the first 18 characters of each file will be the key X(18). I hope my answer is clear.
roshnii,
In your earlier post you mentioned your key is defined as DECIMAL(18,0) which is equivalent to 10 PD field. If you are unloading it it would be just 10 byte PD field and not 18 byte character field.
The Following DFSORT/ICETOOL JCl will give you the desired results. This will give all the matching key records with the value from the parent table inserted in the first 38 bytes followed by child table data making 73 byte file (38+35 bytes)
