Hi,
I would like to store a hierarchy as
Source is
ID parent id
1
2 1
3 2
4 3

i want to have a data store as
id hierarchy
1 NULL
2 1 2
3 1 2 3
4 1 2 3 4

Any way to do this?
Thanks

Karanth,

Just use recursive joins if you have a finite structure. Index the parentid on your table and use something like this -

JOIN CLEAR *
JOIN LEFT_OUTER    id IN table TAG T1 to MULTIPLE parent IN table TAG T2 AS J1
JOIN LEFT_OUTER T1.id IN table        to MULTIPLE parent IN table TAG T3 AS J2
JOIN LEFT_OUTER T2.id IN table        to MULTIPLE parent IN table TAG T4 AS J3

TABLE FILE table
BY T1.id
BY T2.id
BY T3.id
BY T4.id
END

T

Hey TOny

What if I dont need the parent at the end.

I guess it should be like

1
2 1
3 1 2
4 1 2 3

I am working on the same.
Thanks

tony,

the code above does not work for me.
Can you change the code if my table name is t1 and parent field is pid

It gives me error in anyway i change the code.

So given your sample data on your other post of -

Parent Child
A      B
C      D
B      E
F      E
A      F

you need to get

Parent Parent Child
A      B      E
C      D     
A      F      E

Am I correct? If so then you only need two lines of join code but you will need to use LEFT_OUTER. Make sure you understand the TAG syntax and how referencing it within the TABLE request gives you the fields that you want. It is so similar to SQL statements.

And from you other post, check out the search suggestion I gave you, it will help you all that you need.

T

Thanks Tony.. I will look into TAG and will work on it.

Hai Tony,

I am able to get it fine but 1 problem.

As in the above example, I amgetting B and F in the 1st PARENT columns. I should not get them as they are child of other parents.
Can you help me with this.

Thanks

You can also use Drill down reports.....Also if you know OLAP report then it is the easiest way of alll........hope this will help you...............

There are going to be multiple levels of hierarchy, upto 8 levels. What could be the best way? Can anyone suggest me on this.

If you have 8 levels, you need 7 joins. All the information you need to proceed is in this post.

Hey Ginny,

Thats true 7 joins need to be there.
I need to start with nodes which have no parents but just children.some nodes have children and parents, these should not be shown as parents.
But I get these. I am not sure how to eliminate these.

7566 7566 7788 7788 7876
7902 7876
7902 7788 7369
7902 7369
7839 7566 7566 7788
7902
7698 7788
7902
7782 7788
7902

In the above 7566 is both a parent n child, I need it just as a child but not as parent.

Thanks

How about pulling all those without parents from your data to later join from?

T

I am sorry..i could not get it..working on it..
Can you show sample join about what you are suggesting Tony..

Thanks

this is sample code I am using Tony..

JOIN
LEFT_OUTER CHILD IN TABLE_NAME TAG G1 TO
PARENT IN TABLE_NAME TAG G2 AS J20
END

JOIN
LEFT_OUTER G2.CHILD IN TABLE_NAME TO
PARENT IN TABLE TAG G3 AS J21
END

TABLE FILE TABLE_NAME
PRINT
G3.CHILD
BY G1.PARENT AS 'Parent'
BY G1.CHILD AS 'Child'
BY G2.CHILD
END

You can use the following code to show you one method. The first part just creates some sample data for use within the example, everything else should be self explanitory? -

EX -LINES 4 EDAPUT MASTER,HIERARCHY,CF,MEM,FILENAME=HIERARCHY, SUFFIX=FOC,$
SEGNAME=ONE, SEGTYPE=S0 ,$
  FIELD=PARENT, ,A4   ,A4   ,FIELDTYPE=I, $
  FIELD=CHILD, ,A4   ,A4   , $
-RUN
CREATE FILE HIERARCHY
MODIFY FILE HIERARCHY
FREEFORM PARENT CHILD
MATCH PARENT
  ON MATCH REJECT
  ON NOMATCH INCLUDE
DATA
A,B,$
C,D,$
B,E,$
F,E,$
A,F,$
END
-*
TABLE FILE HIERARCHY
   BY CHILD
ON TABLE HOLD AS CHILDREN
END
-*
TABLE FILE HIERARCHY
   BY PARENT
IF PARENT NE (CHILDREN)
ON TABLE HOLD AS ONLY_PARENTS
END
-*
JOIN CLEAR *
JOIN            PARENT   IN ONLY_PARENTS TAG T1 TO MULTIPLE PARENT IN HIERARCHY TAG T2 AS J1
JOIN LEFT_OUTER T2.CHILD IN ONLY_PARENTS        TO          PARENT IN HIERARCHY TAG T3 AS J2
-*
TABLE FILE ONLY_PARENTS
PRINT T2.CHILD
      T3.CHILD
   BY T1.PARENT <-- Not really required as the host table is designed to give Only Parents (obvious really)
   BY T2.PARENT
END

T

This is the way I worked before but it did not give me results I dont know why..strange..!!

Don't mention it, you're welcome

T

Instead of using in file hold I used join..
that worked but I get that are both pareants and child..:-( Its like I need negation of a join..lol

Does anyone have suggestions please?

Hey Tony,

As I said in the above code, if I want to use G3.CHILD in the join, how do I use it. It gives me an error when I hold the file as focus and index it. I used ASNAMES but no use. Can you suggest something

Thanks