Datastage looping

[Dev_India]

Hi Experts,

My Source Table format is :
Col1,Col2
P,K
K,J
L,J
I,H
M,Q
M,O
M,J
J,H
G,C
H,F
F,C
J,D
D,A
C,A
O,E
N,E
E,A
E,B

Col1 and Col2 contains filenames and we have to find full trace of that filename in below format.

We have to start from end node or root node.

Eg. In above example root nodes are A,B,Q , as they exists in only col2 and not in col1

Expected output:

Col, Root,Branch,Seq
A,1,1,1
E,1,1,2
N,1,1,3
A,1,2,1
C,1,2,2
F,1,2,3
H,1,2,4
I,1,2,5
A,1,3,1
D,1,3,2
J,1,3,3
M,1,3,4
A,1,4,1
C,1,4,2
G,1,4,3
A,1,5,1
E,1,5,2
O,1,5,3
M,1,5,4
A,1,6,1
D,1,6,2
J,1,6,3
L,1,6,4
A,1,7,1
D,1,7,2
J,1,7,3
K,1,7,4
P,1,7,5
A,1,8,1
C,1,8,2
F,1,8,3
H,1,8,4
J,1,8,5
L,1,8,6
A,1,9,1
C,1,9,2
F,1,9,3
H,1,9,4
J,1,9,5
M,1,9,6
A,1,10,1
C,1,10,2
F,1,10,3
H,1,10,4
J,1,10,5
K,1,10,6
P,1,10,7
B,2,1,1
E,2,1,2
O,2,1,3
M,2,1,4
B,2,2,1
E,2,2,2
N,2,2,3
Q,3,1,1
M,3,1,2


I need to loop through this file unknown number of times until I end up with the First file in the chain from Last file(end of chain) . It is not one to one in chain , so one file can belong to multiple files.
In output example above for 'A' filename we have multiple branches and sequence number for each branches.

This logic will require loops, so I am not sure whether this can be implemented using tranformer loop functionality or Basic routine in Datastage . Please help


Thanks in advance,
Dev

[asorrell]

I tried to understand your explanation but there seems to be information missing because I cannot follow your example data elements.

Regardless, looping in a transformer would probably not be an efficient way of parsing an entire series of files multiple times. I suspect that writing a BASIC routine will be your best fit.

[Dev_India]

Hi Andy, Thanks for the response, I have added some more comments .

Code: Select all

       P      
       |          
I      K   L        
 \      \ /          
  H<-----J<----M
  |      |    / \
  F   G  | N O   \
   \ /   | \/     \
    C    D E      | 
      \ / / \     |
        A    B    Q          / This is root level /

Source table format is derived from tree structure where Col1 and Col2 contains file names and we have to

find full trace of that filename in below format.
Col1 is 'From filename' and col2 is 'To Filename'

Input Nodes in tabular format:

Source Table format:
Col1,Col2
I,H
G,C
H,F
F,C
J,D
P,K
J,H
K,J
L,J
M,J
M,O
O,E
N,E
D,A
C,A
E,A
E,B
M,Q



Col1 and Col2 are derived from nodes tree where end of chain nodes are A,B,Q

We have to start from end of node or root node.

In above example root nodes are A,B,Q , as they exists in col2 and not in col1.

Expected output format:

Code: Select all

Col, Root, Branch, Seq
A,1,1,1    /* Root Node with first branch of 'A'*/
E,1,1,2
N,1,1,3    /* End of this branch as there is no col2 for 'N'*/
A,1,2,1    /* Root Node with Second branch of 'A'*/
C,1,2,2
F,1,2,3
H,1,2,4
I,1,2,5    /* End of this branch as there is no col2 for 'I'*/
A,1,3,1    /* Root Node with third branch of 'A'*/
D,1,3,2
J,1,3,3
M,1,3,4    /* End of this branch as there is no col2 for 'M'*/
A,1,4,1    /* Root Node with fourth branch  of 'A'*/
C,1,4,2
G,1,4,3    /* End of this branch as there is no col2 for 'G'*/
A,1,5,1    /* Root Node with Fifth branch  of 'A'*/
E,1,5,2
O,1,5,3
M,1,5,4    /* End of this branch as there is no col2 for 'M', two branches are finishing at 'M' */
A,1,6,1    /* Root Node with Sixth branch  of 'A'*/
D,1,6,2
J,1,6,3
L,1,6,4    /* End of this branch as there is no col2 for 'L'*/
A,1,7,1    /* Root Node with Seventh branch  of 'A'*/
D,1,7,2
J,1,7,3
K,1,7,4
P,1,7,5    /* End of this branch as there is no col2 for 'P'*/
A,1,8,1    /* Root Node with Eighth branch  of 'A'*/
C,1,8,2
F,1,8,3
H,1,8,4
J,1,8,5
L,1,8,6    /* End of this branch as there is no col2 for 'L',two branches are finishing at 'L' */
A,1,9,1    /* Root Node with Nineth branch  of 'A'*/
C,1,9,2
F,1,9,3
H,1,9,4
J,1,9,5
M,1,9,6    /* End of this branch as there is no col2 for 'M', third branch finishing at 'M' */
A,1,10,1   /* Root Node with Tenth branch  of 'A'*/
C,1,10,2
F,1,10,3
H,1,10,4
J,1,10,5
K,1,10,6
P,1,10,7   /* End of this branch as there is no col2 for 'P',two branches are finishing at 'P'*/
B,2,1,1    /* Root Node with First branch  of 'B'*/
E,2,1,2
O,2,1,3
M,2,1,4    /* End of this branch as there is no col2 for 'M'*/
B,2,2,1    /* Root Node with Second branch  of 'B'*/
E,2,2,2
N,2,2,3    /* End of this branch as there is no col2 for 'N'*/
Q,3,1,1    /* Root Node with First branch  of 'Q'*/
M,3,1,2    /* End of this branch as there is no col2 for 'M'*/

I do not have much experience of Basic Routines. Please help.

Thanks,
Dev
[note - Added some formatting to make it easier to read - Andy]

[chulett]

What database is this? Most would have "hierarchical" queries that should handle requirements like this. Or at least help.

[Dev_India]

Hi Craig

Thanks for response,

Its oracle DB.

Thanks,
Dev

[chulett]

Oracle Hierarchical Queries