creating an universe

[Former Member]

HI All,

I have a table with hierarchy inside. i need to create a query for datafoundationlayer.

i have a query but is giving an error saying invalid alias.  Can someone help me out please.

WITH TaskHierarchy (name, taskid,parentid,levelnumber,creator,userparameter2,usercomment) AS
(
  -- Base case
  SELECT
  name, taskid,parentid, 1 as levelnumber, creator,userparameter2,usercomment
  FROM task
  WHERE parentid IS NULL

  UNION ALL

  -- Recursive step
  SELECT
  e.name, e.taskid,e.parentid
  eh.levelnumber + 1 AS levelnumber
, e.creator,e.userparameter2,e.usercomment
  FROM task e
  INNER JOIN TaskHierarchy eh ON
  e.parentid = eh.taskID
)

SELECT *
FROM TaskHierarchy
ORDER BY levelnumber

[Former Member · Accepted solution]

Business Objects doesn't like the "WITH" syntax. Use this instead to create a derived table:

SELECT name, taskid,parentid,levelnumber,creator,userparameter2,usercomment from
(
  -- Base case
  SELECT
  name, taskid,parentid, 1 as levelnumber, creator,userparameter2,usercomment
  FROM task
  WHERE parentid IS NULL

  UNION ALL

  -- Recursive step
  SELECT
  e.name, e.taskid,e.parentid
  eh.levelnumber + 1 AS levelnumber
, e.creator,e.userparameter2,e.usercomment
  FROM task e
  INNER JOIN TaskHierarchy eh ON
  e.parentid = eh.taskID
)
TaskHierarchy


[Former Member]

Hi,

Simple use following query:

select from (

SELECT

  name, taskid,parentid, 1 as levelnumber, creator,userparameter2,usercomment

  FROM task

  WHERE parentid IS NULL

  UNION ALL

  SELECT

  e.name, e.taskid,e.parentid

  eh.levelnumber + 1 AS levelnumber

, e.creator,e.userparameter2,e.usercomment

  FROM task e

  INNER JOIN TaskHierarchy eh ON

  e.parentid = eh.taskID)

ORDER BY levelnumber

thanks,

swapnil