안녕하세요 

ORACLE 개발하다가 MS SQL 로 쿼리 짤라니까

안되는 점이 너무 많네요 

도움좀 받고 싶어서 글을 남기게 되었습니다.

혼자 찾아 보는것도 지치네요 ....

오라클 같으면 제약 없이 계층형(CTE) 조건문에 쓰는데

MS SQL은 WITH 위에 조건이 있으면 안되는거같더라구요 

2가지 방법으로 접근해 봤는데 

다른방법 있으면 지도좀 부탁드립니다.

-- 방법 1

SELECT H.ID ,  E.ID 

FROM USER H, ERP E

WHERE H.USE_YN=1 AND E.CNT > 0

UNION ALL

SELECT H.ID ,  E.ID 

FROM USER H, ERP E

WHERE E.ID IN 

(

-- 이부분이 안됩니다 ㅠ.ㅠ MS SQL 에서는 WITH 문 위에 다른것들을 못넣더라구요...

-- 이 위에 또 UNION ALL 절이 있어서 WITH 문을 앞으로 뺄수가 없어요 ... 

  WITH TREE

  AS ( SELECT ID FROM ERP WHERE ID = H.ID -- (상위에 값을 START WITH)

  UNION ALL

  SELECT ID FROM ERP AS CHILD , TREE PARENT

   WHERE CHILID.PARENTID = PARENT.ID )

  SELECT ID FROM TREE

)



--  방법2

SELECT H.ID ,  E.ID

FROM USER H, ERP E

WHERE H.USE_YN=1 AND E.CNT > 0

UNION ALL

SELECT H.ID ,  E.ID

FROM USER H, ERP E

WHERE E.ID IN

(

-- 함수를 만들어서 호출하는 방법.  함수는 CTE문

-- 하지만 리턴값이 다중 ROW인데 첫번째 값만 리턴네요 ;; 함수는 바로 밑에 써볼께요 

dbo.treeCte(H.id) 

)

-- 함수문

CREATE FUNCTION treeCte (@ID NUMERIC)

RETURNS NUMERIC(8)  -- 다중 ROW 값으로 리턴 받으려면 어떻게 선언해야되는지 모르겠네요 .. NUMERIC 으로 선언하니 첫번째 값만 리턴합니다..

AS

BEGIN

DECLARE @V_RETURN NUMERIC(8)

WITH TREE

  AS ( SELECT ID FROM ERP WHERE ID = @ID

  UNION ALL

  SELECT ID FROM ERP AS CHILD , TREE PARENT

   WHERE CHILID.PARENTID = PARENT.ID )

  SELECT @V_RETURN=ID FROM TREE

  RETURN @V_RETURN;

END