hierarchical query on wdata table
Udzielono odpowiedziIs there way to write hierarchy query on wdata table ? I have a table with parent, child relationship information.
Would like to write query to display hierarchy.
Does Wdata support connect by prior as in Oracle database or Recursive common table expression as in MYSQL database to display hierarchical data?
Please advise
-
Hi Naren Kumar,
Yes, you can write a query to show the hierarchy. Here is an example that may not be exactly what you're looking for, but a starting place. For this example, lets say that you have a dimension table name "Hierarchy" with the columns "id", "parent_id", and "name". Also assume that "id" is a key and the table has the following data in it:
id,parent_id,name
1,,Root
2,1,Child 1
3,1,Child 2
4,2,Grandchild 1
5,2,Grandchild 2Recursive WITH queries are not supported in Wdata, but the following example query is another way to display the hierarchy of the above sample data. Keep in mind that you will need to reference the table by Worksapce ID and Table ID for it to work in Wdata.
SELECT
H1.id AS Level1_ID, H1.name AS Level1_Name,
H2.id AS Level2_ID, H2.name AS Level2_Name,
H3.id AS Level3_ID, H3.name AS Level3_Name
FROM
Hierarchy H1
LEFT JOIN
Hierarchy H2 ON H2.parent_id = H1.id
LEFT JOIN
Hierarchy H3 ON H3.parent_id = H2.id
WHERE H1.parent_id IS NULLThe example output of the query is:
0Zaloguj się, aby dodać komentarz.
Komentarze
Komentarze: 1