• 

  Jeff Hickey

  • 8 november 2024 20:19

  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 2

  Recursive 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 NULL

  The example output of the query is:

  

  0

U moet u aanmelden om een opmerking te plaatsen.