0

I am trying to convert the following Oracle query to SQL Server and have run into an issue trying to create my CTE for the hierarchy order. I only provided the last select statement as there are 7 with statements and thought the code would be too long to post. Any ideas? 

SELECT '"'||rownum||'"' as ORIGINAL_ORDER, A.*

FROM (
SELECT 

 '"'||LEVEL||'"'
, CAST('"'||lpad(' ',level*2,' ')||P.POSITION_NBR||'"' AS VARCHAR(100)) AS 
HIERARCHY
,'"'||P.POSITION_NBR||'"' AS POS_NBR
,'"'||P.DESCR||'"' AS POS_TITLE
,'"'||P.CLASSIFICATION_CD||'"' AS CLASSCd
, '"'||P.LOCATION||'"' AS LOC
, '"'||P.LOC_DESCR||'"' AS LOC_DESCR
, '"'||P.DEPTID||'"' AS DEP
, '"'||P.DEPT_DESCR||'"' AS DEP_DESCR
, F.LANG_PROF
, A.EMPS
, B.ACCOMPLISHMENT "ACC_READ"
, B.DT_ISSUED "DT_ISSUED_READ"
, B.RESULTS_LEVEL_CD "LEVEL_CD_READ"
, B.RESULTS_EXPIRY_DT "EXPIRY_DT_READ"
, C.ACCOMPLISHMENT "ACC_WRITE"
, C.DT_ISSUED "DT_ISSUED_WRITE"
, C.RESULTS_LEVEL_CD "LEVEL_CD_WRITE"
, C.RESULTS_EXPIRY_DT "EXPIRY_DT_WRITE"
, D.ACCOMPLISHMENT "ACC_ORAL"
, D.DT_ISSUED "DT_ISSUED_ORAL"
, D.RESULTS_LEVEL_CD "LEVEL_CD_ORAL"
, D.RESULTS_EXPIRY_DT "EXPIRY_DT_ORAL"
, E.XLATLONGNAME


   from   POSITIONS P

LEFT JOIN JOB A
ON P.POSITION_NBR = A.POSITION_NBR

LEFT JOIN SLE_READ B
ON A.EMPLID = B.EMPLID

LEFT JOIN SLE_WRITE C
ON A.EMPLID = C.EMPLID

LEFT JOIN SLE_ORAL D
ON A.EMPLID = D.EMPLID

LEFT JOIN POS_LANG_REQ E
ON A.POSITION_NBR = E.POSITION_NBR

LEFT JOIN POS_LANG_PROF F
ON P.POSITION_NBR = F.POSITION_NBR

start with P.position_nbr = '&&pPOSN_STARTSAT'`enter code here`
connect by nocycle prior p.position_nbr = p.reports_to 

order siblings by p.position_nbr ) A
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jason
  • 1
  • What are you trying to do with those `||` characters? What error are you getting when you try to execute this in SQL Server? – Tab Alleman Jun 01 '18 at 18:40
  • 1
    @TabAlleman || is how you concatenate strings in oracle. – Sean Lange Jun 01 '18 at 18:41
  • In that case, replace all `||` sequences with `+` – Tab Alleman Jun 01 '18 at 18:42
  • Are you just asking how to translate the CONNECT BY syntax in Oracle with Recursive CTE syntax that SQL Server supports? https://blog.sqlauthority.com/2012/04/24/sql-server-introduction-to-hierarchical-query-using-a-recursive-cte-a-primer/ If it is just a matter of conversion, this thread might help https://stackoverflow.com/questions/959804/simulation-of-connect-by-prior-of-oracle-in-sql-server?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa – Error_2646 Jun 01 '18 at 18:43
  • 1
    I think what would help you the most is not trying to convert this from oracle. Instead post the details of your tables along with some sample data and the desired output. Often what works in on DBMS is a terrible way to do it in another. Here is a great place to start. http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ You also might want to take a peek here. https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3 – Sean Lange Jun 01 '18 at 18:43

0 Answers0