Heirarchy Logic

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Heirarchy Logic

Post by pavans »

Hi All,

I have below requirement to be implemented.

A
| |
B C
|
D

Result should be :

Parent Child Top_flag Bottom_flag ChildLevel ParentLevel
A B Y Y 2 1
A C Y N 2 1
C D N Y 3 2
A D Y Y 3 1
A A Y N 1 1
B B N Y 2 2
C C N N 2 2
D D N Y 3 3



I have written a recursive sql query which is giving me the result as

WITH temp_A (s_id, id_over, id_under, TYPE_ID, t_tp, iteration ) AS
(
SELECT surr_id, t_id_over, t_id_under, TYPE_ID,t_tp,1
FROM
A
WHERE
TYPE_ID in ('L', 'B')
UNION ALL SELECT b.s_id, a.id_over, b.ID_UNDER, a.TYPE_ID,b.t_tp, a.iteration + 1
FROM temp_A AS a, A AS b
WHERE a.id_under = b.ID_OVER
and b.TYPE_ID in ('L', 'B')
and b.t_tp <> 'D'
)
SELECT s_id, id_under, id_over, TYPE_ID,isrt_tp, iteration
FROM temp_A

Result which I am getting:

Parent Child Iteration
A B 1
A C 1
C D 1
A D 2

I am still looking at the logic for Parent, Child columns and the flags logic.

Any help is really appreciated.
Thanks,
Pavan
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Start by enclosing your examples in Code tags, so we can understand what's happening.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

ray.wurlod wrote:Start by enclosing your examples in Code tags, so we can understand what's happening. ...
I am sorry for not putting it properly.
Here we go:

Code: Select all

  A
           |   |
           B   C
               |
               D
Result should be :

Code: Select all

Parent Child  Top_F Bottom_F Child_Level Parent_Level
'A'	 'B'	    'Y'		'Y'		'2'		'1'
'A'	'C'	   'Y'		'N'		'2'		'1'
'C'	'D'	   'N'		'Y'		'3'		'2'
'A'	'D'	  'Y'		'Y' 		'3'		'1'
'A'	'A'	  'Y'		'N'		'1'		'1'
'B'	'B'	  'N'		'Y' 		'2'		'2'
'C'	'C'	  'N'		'N'		'2'		'2'
'D'	'D'	  'N'		'Y'		'3'		'3'
By using the above mentioned query I am able to get :

Code: Select all

Parent Child  Iteration
'A'	 'B'	    1
'A'	'C'	    1
'C'	'D'	    1
'A'	'D'	   2
Thanks,
Pavan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

:!: You still hadn't put anything properly so I edited your post and added the

Code: Select all

 tags for you. Note that I made absolutely no attempt to ensure anything lined up, you can go back and do that.



 :idea: 

Peoples, the 'preview' function [b]is there for a reason.[/b] Make use of it. The forum software removes all 'extra' whitespace from [b]everything[/b] you post [i]unless[/i] you wrap it in code tags. And being a proportional font, it won't line up right away as you might expect, so make your fancy examples, preview, fix, preview, fix, preview, et etc until it makes sense.

 :idea:
-craig

"You can never have too many knives" -- Logan Nine Fingers
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

chulett wrote::!: You still hadn't put anything properly so I edited your post and added the

Code: Select all

 tags for you. Note that I made absolutely no attempt to ensure anything lined up, you can go back and do that.



 :idea: 

Peoples, the 'preview' function [b]is there for a reason.[/b] Make use of it. The forum software removes all 'extra' whitespace from [b]everything[/b] you post [i]unless[/i] you wrap it in code tags. And being a proportional font, it won't line up right away as you might expect, so make your fancy examples, preview, fix, preview, fix, preview, et etc until it makes sense.

 :idea:[/quote]



   A  ----B
      -----C---D

The picture depicts that:
A Is parent to B
A is parent to C
C is Parent to D 
A is parent to D
also, A is child to itself
        B is child to itself
        C is child to itself
        D is child to itself. The last four records in the below examples expalins that.

Result should be :

Parent,Child,Top_flag,Bottom_flag,ChildLevel,ParentLevel
A,B,Y,Y,2,1
A,C,Y,N,2,1
C,D,N,Y,3,2
A,D,Y,Y,3,1
A,A,Y,N,1,1
B,B,N,Y,2,2
C,C,N,N,2,2
D,D,N,Y,3,3



I have written a recursive sql query which is giving me the result as 

WITH temp_A (s_id, id_over, id_under, TYPE_ID, t_tp, iteration ) AS
(
SELECT surr_id, t_id_over, t_id_under, TYPE_ID,t_tp,1
FROM 
A
WHERE 
TYPE_ID in ('L', 'B') 
UNION ALL SELECT b.s_id, a.id_over, b.ID_UNDER, a.TYPE_ID,b.t_tp, a.iteration + 1
FROM temp_A AS a, A AS b
WHERE a.id_under = b.ID_OVER
and b.TYPE_ID in ('L', 'B')
and b.t_tp <> 'D' 
) 
SELECT s_id, id_under, id_over, TYPE_ID,isrt_tp, iteration
FROM temp_orgchart

Result:

Parent,Child,Iteration
A,B,1
A,C,1
C,D,1
A,D,2


I tried it putting the way I understood [Code ] tags from other forums.
if I am not correct then please let me know.

Any ideas on the logic or doing it in a more optimal way.
Thanks,
Pavan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

[sigh]

Picture? What picture? Did you not read anything I posted? :?
-craig

"You can never have too many knives" -- Logan Nine Fingers
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

chulett wrote:[sigh]

Picture? What picture? Did you not read anything I posted? :?
chulett,

I tried my best to arrange it. I may be incorrect, but i wanted to put my understanding of the problem in the forum to get some help.
Can you please let me know which part of my post is not clear and the way you like me put it.
Thanks,
Pavan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

My whole point is that there are no code tags in your post. Look again at your "picture" and tell me how much that depicts as it stands now. You needed to spell everything out in words at which point the picture is pretty well moot.

Something like this could stand on its own, however:

Code: Select all

A ----B 
  ----C---D
As noted several times, the tags allow whitespace to remain, typically used to preserve formatting / indenting in actual code but useful for ASCII art as well. Do a 'Reply with quote' of this post and look at what you see for the picture before you actually submit it and you'll see the tags. And then don't submit it. :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

chulett wrote:My whole point is that there are no code tags in your post. Look again at your "picture" and tell me how much that depicts as it stands now. You needed to spell everything out in words at which point the picture is pretty well moot.

Something like this could stand on its own, however:

Code: Select all

A ----B 
  ----C---D
As noted several times, the tags allow whitespace to remain, typically used to preserve formatting / indenting in actual code but useful for ASCII art as well. Do a 'Reply with quote' of this post and look at what you see for the picture before you actually submit it and you'll see the tags. And then don't submit it. :wink:
Chulett,
Can you please ignore that and consider the below one and help me out:

A Is parent to B
A is parent to C
C is Parent to D
A is parent to D -- The first four records in the below example shows it.
also, A is child to itself
B is child to itself
C is child to itself
D is child to itself. The last four records in the below examples expalins that.

Result should be :

Parent,Child,Top_flag,Bottom_flag,ChildLevel,ParentLevel
A,B,Y,Y,2,1
A,C,Y,N,2,1
C,D,N,Y,3,2
A,D,Y,Y,3,1
A,A,Y,N,1,1
B,B,N,Y,2,2
C,C,N,N,2,2
D,D,N,Y,3,3
Thanks,
Pavan
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Nope, sorry, can't ignore stuff like that. And at this point in my day, I need to leave the "help me out" part to others.
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

How about some business-like specifications for the derived fields? For example it seems to me that:
  • Top_Flag = "has no parents"

    Bottom_Flag = "has no children"

    Child_Level = level in hierarchy and must be one larger than that of its direct parent except for self-referential relationships where it must be the same as that of itself

    Parent_Level = level in hierarchy and must be one less than that of its direct children except for self-referential relationships where it must be the same as that of itself
Do those accord with your understanding? If not, please offer alternatives.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

ray.wurlod wrote:How about some business-like specifications for the derived fields? For example it seems to me that:
  • Top_Flag = "has no parents"

    Bottom_Flag = "has no children"

    Child_Level = level in hi ...
Thanks Ray.

For top_flag and bottom_flag the logic is

for the 1st row :
A is the parent at the top level so Top_flag is 'Y' and B doesnt have a child so Bottom_Flag is 'Y'

for the 2nd row:

A is the parent at the top level so Top_flag is 'Y' and C has a child so Bottom_Flag is 'N'

for the 3rd row:

C is the parent and not at the top level so Top_flag is 'N' and D has no child so Bottom_Flag is 'Y'

ChildLevel and ParentLevel are indicates what level the child and parent are for that row.
Thanks,
Pavan
jcthornton
Premium Member
Premium Member
Posts: 79
Joined: Thu Mar 22, 2007 4:58 pm
Location: USA

Post by jcthornton »

Pavan,

After working through the provided information (thanks for tagging it chulett) and I see one glaring omission: what does that data look like in the database table(s) - not the results from your query [which clearly identifies that we are getting only a tiny bit of information about your problem].

While this requirement could probably be done through a single SQL query, it does not need to be. You are using DataStage after all. You have an option to do separate queries for to identify your 'Top Flag' groups, your 'Bottom Flag' groups, etc. and use lookups to set your flags correctly.

Doing that would allow you to focus your main SQL query on building the hierarchy and identifying your levels. Even this doesn't have to be done in SQL - as you could get your relationships from your source table and use different stages to build the hierarchy yourself (the downside comes if your potential hierarchy is many levels deep - or unlimited) if your RDBMS does not support recursive queries.
Jack Thornton
----------------
Spectacular achievement is always preceded by spectacular preparation - Robert H. Schuller
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Yes, what database is this coming from? All these examples and pictures and nary a mention of what database you're working with. May be useful information after all.
-craig

"You can never have too many knives" -- Logan Nine Fingers
pavans
Participant
Posts: 116
Joined: Sun Sep 10, 2006 7:33 am
Location: bangalore, india

Post by pavans »

chulett wrote:Yes, what database is this coming from? All these examples and pictures and nary a mention of what database you're working with. May be useful information after all.
Thanks for the replies.

Database is : SQL Server 2008

Data from database tables:
SURR_ID,Status_ID,ID_OVER,ID_UNDER,TYPE_ID,
60001,I,0000000002,0000000033,L
60002,I,0000000002,0000000091,L
60003,I,0000000002,0000000011,L
60004,I,0000000033,0000000011,L

The levels in the hierarchy is many levels deep.
The query which I posted is giving me only a part of the required output.

I have tried with Stage Variables but failed very badly. :(
I will give a try with Lookups.
Thanks,
Pavan
Post Reply