Tranformer logic

kennyapril · Post by **kennyapril** » Thu Oct 06, 2011 8:22 am

I have three fields node,node_level,path
which has values like below
node :A|G|J|H|B|I|C|E|
next_level:X|X|G|G|A|G|A|B|
Path:/X|/X|/X/G|/X/G|/X/A|/X/G|/X/A|/X/A/B|

ignore the pipe(|) in between the records for the three fields and the rest are the records
in the table.

If lets say the next_level expires I need to expire all the nodes in the below path.

So lets say G expires then G,J,H should be expired and If A expires then A,B,E should be expired.

I have a design to expire the beginning level but I need to find their nodes in all the levels based on the path and node from the next_level

please suggest the code which can be used in the transformer to find out the nodes.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 06, 2011 3:06 pm

That "cascading delete" does not look like an ETL task to me.

kennyapril · Post by **kennyapril** » Fri Oct 07, 2011 8:27 am

Actually if a node is expired its below level nodes are to be expired and then its below levels nodes till the end of the tree are expired.
they are related with node , next_level(toplevel above the node) and path also which shoes the path(ex last path X/A/B If a expires then B and then nodes
for which B is the next_level.
one more note is there will be only 1 level after the last node in the path

so I need a function in the transformer to find some nodes based on the path and then its nodes for which it is a next_level.

Is there any cascade function to this requirement?

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2011 9:17 am

No.

DataStage takes only one pass through the data. It can "remember" and access only the previous row.

kennyapril · Post by **kennyapril** » Fri Oct 07, 2011 9:51 am

Ok. I will try to check for each pass by comparing as the records are sorted.

In the path field below Is there a function to find the node next to the node which is found.

I mean lets say If the input record coming to the tramsformer Is X It needs to check the path records and find it and then next nodes after it by finding the '/' till the end like A and then B.

Path:/X|/X|/X/G|/X/G|/X/A|/X/G|/X/A|/X/A/B|

Thanks

ray.wurlod · Post by **ray.wurlod** » Fri Oct 07, 2011 5:05 pm

Not unless you write one yourself. I don't think it's that easy a task. Try formulating the specification in English of what you would need to do to implement this logic, and we can take a look at that to see whether any solution springs to mind.

Is the longest path likely always to be the final one in the list? If so there's an easy solution.