I did some converting, and was mostly able to move all of the tables defined using "WITH" into sub tables, it's much bigger and messier since each time a table is referenced I have to put the whole virtual table in but it runs. I just wasn't able to get
Select * from (with tablename as(select query) select columns from tablename;) to work. I'm not sure what I missed. But here's what I came up with. The one remaining question is, did I properly handle the invest_tree table conversation. I ask because the original code references the table IN the table defenition.
INVEST_TREE (ID, NAME, PARENT_ID, DEPTH, ROOT_ID, HG_HAS_CHILDREN) as
(
SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
FROM CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
UNION ALL
SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN
FROM CHILD_INVESTMENTS ci
INNER JOIN INVEST_TREE iv ON ci.PARENT_ID = iv.ID
)
You see on the final line it links Invest_tree to CI, but this is done inside the defenition of invest_tree which means it refers to itself?
Anyway here's what I did in case anyone is interested in the MSSQL version of the NSQL.
SELECT
@SELECT:DIM:USER_DEF:IMPLIED:X:X.UNIQUEID:UNIQUEID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.HG_HAS_CHILDREN:HG_HAS_CHILDREN@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PARENT_ID:PARENT_ID@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRNAME:PRNAME@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRFINISH:PRFINISH@
, @SELECT:DIM_PROP:USER_DEF:IMPLIED:X:X.PRSTART:PRSTART@
, @SELECT:DIM_PROP:USER_DEF:BOOLEAN:X:X.ACTIVE:ACTIVE@
FROM
(
SELECT
UNIQUEID
, HG_HAS_CHILDREN
, PARENT_ID
, PRNAME
, PRFINISH
, PRSTART
, ACTIVE
FROM
(
SELECT inv.ID UNIQUEID
, iv.depth
, iv.HG_HAS_CHILDREN
, iv.PARENT_ID
, inv.NAME PRNAME
, inv.SCHEDULE_FINISH PRFINISH
, inv.SCHEDULE_START PRSTART
, inv.IS_ACTIVE ACTIVE
FROM
(
SELECT
DISTINCT
ID
, NAME
, PARENT_ID
, DEPTH
, ROOT_ID
, HG_HAS_CHILDREN
FROM
(
SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) as CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
UNION ALL
SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) as ci
INNER JOIN
(
SELECT
ID
, NAME
, PARENT_ID
, 0 AS DEPTH
, ID AS ROOT_ID
, HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT
inv.ID
, inh.PARENT_ID
, inh.CHILD_ID
, inp.PARENT_ID AS HG_HAS_CHILDREN
, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) as CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
)iv ON ci.PARENT_ID = iv.ID
) as INVEST_TREE
) iv
INNER JOIN inv_investments inv ON iv.id = inv.id
INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id
) AS Invest_NSQL
WHERE Depth = isnull(@where:param:user_def:string:hg_row_id@, 0)
UNION
SELECT
UNIQUEID
, HG_HAS_CHILDREN
, PARENT_ID
, PRNAME
, PRFINISH
, PRSTART
, ACTIVE
FROM
(
SELECT inv.ID UNIQUEID
, iv.depth
, iv.HG_HAS_CHILDREN
, iv.PARENT_ID
, inv.NAME PRNAME
, inv.SCHEDULE_FINISH PRFINISH
, inv.SCHEDULE_START PRSTART
, inv.IS_ACTIVE ACTIVE
FROM
(
SELECT
DISTINCT
ID
, NAME
, PARENT_ID
, DEPTH
, ROOT_ID
, HG_HAS_CHILDREN
FROM
(
SELECT ID, NAME, PARENT_ID, 0 AS DEPTH, ID AS ROOT_ID, HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) as CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
UNION ALL
SELECT ci.ID, ci.NAME, ci.PARENT_ID, DEPTH + 1, ROOT_ID, ci.HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT inv.ID, inh.PARENT_ID, inh.CHILD_ID, inp.PARENT_ID AS HG_HAS_CHILDREN, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) AS ci
INNER JOIN
(
SELECT
ID
, NAME
, PARENT_ID
, 0 AS DEPTH
, ID AS ROOT_ID
, HG_HAS_CHILDREN
FROM
(
SELECT DISTINCT
inv.ID
, inh.PARENT_ID
, inh.CHILD_ID
, inp.PARENT_ID AS HG_HAS_CHILDREN
, inv.NAME
FROM INV_INVESTMENTS inv
LEFT OUTER JOIN INV_HIERARCHIES inh on inv.ID = inh.CHILD_ID AND inh.PARENT_ID IS NOT NULL
LEFT OUTER JOIN INV_HIERARCHIES inp on inv.ID = inp.PARENT_ID
) as CHILD_INVESTMENTS
WHERE PARENT_ID IS NULL
)iv ON ci.PARENT_ID = iv.ID
) as INVEST_TREE
) iv
INNER JOIN inv_investments inv ON iv.id = inv.id
INNER JOIN ODF_CA_PROJECT odfp ON iv.id = odfp.id
) AS Invest_NSQL
WHERE PARENT_ID = @where:param:user_def:string:hg_row_id@
) X
WHERE @FILTER@