Oracle To SQL Server Help

[ranjith23]

Can you help me converting below SQL from Oracle to SQL Server? (START WITH & CONNECT BY are oracle functions)

SELECT DISTINCT
N.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,

FROM PSTREENODE N , PSTREENODE B1
WHERE N.SETID = B1.SETID
AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE
AND N.TREE_NAME = B1.TREE_NAME
AND N.EFFDT = B1.EFFDT

START WITH N.TREE_NODE_NUM = (
  SELECT N1.TREE_NODE_NUM
    FROM PSTREENODE N1
   WHERE N1.SETID = N.SETID
     AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
     AND N1.TREE_NAME = B1.TREE_NAME
     AND N1.EFFDT = N.EFFDT
     AND N1.TREE_NODE = B1.TREE_NODE)
CONNECT BY PRIOR N.PARENT_NODE_NUM = N.TREE_NODE_NUM

ORDER BY N.TREE_NODE_NUM

[karthikn]

45 minutes ago, ranjith23 said:

Can you help me converting below SQL from Oracle to SQL Server? (START WITH & CONNECT BY are oracle functions)

SELECT DISTINCT
N.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,

FROM PSTREENODE N , PSTREENODE B1
WHERE N.SETID = B1.SETID
AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE
AND N.TREE_NAME = B1.TREE_NAME
AND N.EFFDT = B1.EFFDT

START WITH N.TREE_NODE_NUM = (
  SELECT N1.TREE_NODE_NUM
    FROM PSTREENODE N1
   WHERE N1.SETID = N.SETID
     AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
     AND N1.TREE_NAME = B1.TREE_NAME
     AND N1.EFFDT = N.EFFDT
     AND N1.TREE_NODE = B1.TREE_NODE)
CONNECT BY PRIOR N.PARENT_NODE_NUM = N.TREE_NODE_NUM

ORDER BY N.TREE_NODE_NUM

try this..rough guess

WITH CTE AS (
  SELECT N1.TREE_NODE_NUM AS TREE_NODE_NUM
    FROM PSTREENODE N1
   WHERE N1.SETID = N.SETID
     AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
     AND N1.TREE_NAME = B1.TREE_NAME
     AND N1.EFFDT = N.EFFDT
     AND N1.TREE_NODE = B1.TREE_NODE)
     
SELECT DISTINCT
CTE.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,
FROM PSTREENODE N

INNER JOIN  PSTREENODE B1

ON N.SETID = B1.SETID
AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE
AND N.TREE_NAME = B1.TREE_NAME
AND N.EFFDT = B1.EFFDT

INNER JOIN CTE ON N.PARENT_NODE_NUM = CTE.TREE_NODE_NUM

ORDER BY CTE.TREE_NODE_NUM

 

[Amrita]

1 minute ago, karthikn said:

try this..rough guess

WITH CTE AS (
  SELECT N1.TREE_NODE_NUM AS TREE_NODE_NUM
    FROM PSTREENODE N1
   WHERE N1.SETID = N.SETID
     AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
     AND N1.TREE_NAME = B1.TREE_NAME
     AND N1.EFFDT = N.EFFDT
     AND N1.TREE_NODE = B1.TREE_NODE)
     
SELECT DISTINCT
CTE.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,
FROM PSTREENODE N , PSTREENODE B1
WHERE N.SETID = B1.SETID
AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE
AND N.TREE_NAME = B1.TREE_NAME
AND N.EFFDT = B1.EFFDT
INNER JOIN N.PARENT_NODE_NUM = CTE.TREE_NODE_NUM
ORDER BY CTE.TREE_NODE_NUM

[papacha]

Can you also try this?

 

;WITH RecursiveCTE AS
(
    -- Anchor (START WITH)
SELECT DISTINCT
N.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,
Level = 0
FROM PSTREENODE N
JOIN PSTREENODE B1 ON N.SETID = B1.SETID AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE AND N.TREE_NAME = B1.TREE_NAME AND N.EFFDT = B1.EFFDT
WHERE N.TREE_NODE_NUM = (
    SELECT N1.TREE_NODE_NUM FROM PSTREENODE N1
    WHERE N1.SETID = N.SETID
    AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
    AND N1.TREE_NAME = B1.TREE_NAME
    AND N1.EFFDT = N.EFFDT
    AND N1.TREE_NODE = B1.TREE_NODE
    )
    

    UNION ALL

    -- Recursive join
    SELECT DISTINCT
    N.TREE_NODE_NUM,
    N.TREE_LEVEL_NUM,
    N.TREE_NODE
        Level = R.Level + 1
    FROM PSTREENODE N
         JOIN PSTREENODE B1 ON N.SETID = B1.SETID AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE AND N.TREE_NAME = B1.TREE_NAME AND N.EFFDT = B1.EFFDT
         JOIN RecursiveCTE R ON R.TREE_NODE_NUM = C.PARENT_NODE_NUM
)
SELECT
    R.*
FROM
    RecursiveCTE AS R

 

[karthikn]

1 minute ago, Amrita said:

 

3 minutes ago, karthikn said:

try this..rough guess

WITH CTE AS (
  SELECT N1.TREE_NODE_NUM AS TREE_NODE_NUM
    FROM PSTREENODE N1
   WHERE N1.SETID = N.SETID
     AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
     AND N1.TREE_NAME = B1.TREE_NAME
     AND N1.EFFDT = N.EFFDT
     AND N1.TREE_NODE = B1.TREE_NODE)
     
SELECT DISTINCT
CTE.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,
FROM PSTREENODE N

INNER JOIN  PSTREENODE B1

ON N.SETID = B1.SETID
AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE
AND N.TREE_NAME = B1.TREE_NAME
AND N.EFFDT = B1.EFFDT

INNER JOIN CTE ON N.PARENT_NODE_NUM = CTE.TREE_NODE_NUM

ORDER BY CTE.TREE_NODE_NUM

 

mallosari chudagane wrogn ani clarity came.. you cannot join N1 and N in CTE..

[karthikn]

5 minutes ago, papacha said:

Can you also try this?

 

;WITH RecursiveCTE AS
(
    -- Anchor (START WITH)
SELECT DISTINCT
N.TREE_NODE_NUM,
N.TREE_LEVEL_NUM,
N.TREE_NODE,
Level = 0
FROM PSTREENODE N
JOIN PSTREENODE B1 ON N.SETID = B1.SETID AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE AND N.TREE_NAME = B1.TREE_NAME AND N.EFFDT = B1.EFFDT
WHERE N.TREE_NODE_NUM = (
    SELECT N1.TREE_NODE_NUM FROM PSTREENODE N1
    WHERE N1.SETID = N.SETID
    AND N1.SETCNTRLVALUE = N.SETCNTRLVALUE
    AND N1.TREE_NAME = B1.TREE_NAME
    AND N1.EFFDT = N.EFFDT
    AND N1.TREE_NODE = B1.TREE_NODE
    )
    

    UNION ALL

    -- Recursive join
    SELECT DISTINCT
    N.TREE_NODE_NUM,
    N.TREE_LEVEL_NUM,
    N.TREE_NODE
        Level = R.Level + 1
    FROM PSTREENODE N
         JOIN PSTREENODE B1 ON N.SETID = B1.SETID AND N.SETCNTRLVALUE = B1.SETCNTRLVALUE AND N.TREE_NAME = B1.TREE_NAME AND N.EFFDT = B1.EFFDT
         JOIN RecursiveCTE R ON R.TREE_NODE_NUM = C.PARENT_NODE_NUM
)
SELECT
    R.*
FROM
    RecursiveCTE AS R

 

recursive CTE is the way to go.. 

[Amrita]

4 minutes ago, karthikn said:

 

mallosari chudagane wrogn ani clarity came.. you cannot join N1 and N in CTE..

nenu annadi helping ki good job. nenu asalu query chudaledu 

[karthikn]

28 minutes ago, Amrita said:

nenu annadi helping ki good job. nenu asalu query chudaledu 

nuvvu sql lo thope thurum ani utah lo talk

[Amrita]

9 minutes ago, karthikn said:

nuvvu sql lo thope thurum ani utah lo talk

A rumor start chesindi nuvve ani FBI info