During converting Oracle statements into SQL Server T-SQL, there seems no direct equivalent of Oracle PL/SQL CONNECT BY PRIOR ... START WITH statement in SQL Server. The SQL standard way to implement recursive queries, is the
WITH clause (recursive CTE). Oracle PL/SQL example:
select UserID, FirstName, LastName
from Users
WHERE UserTypeID = 5
CONNECT BY PRIOR UserID = ParentID START WITH UserID = 2320
ORDER BY LastName
from Users
WHERE UserTypeID = 5
CONNECT BY PRIOR UserID = ParentID START WITH UserID = 2320
ORDER BY LastName
SQL Server T-SQL code:
WITH N (Userid, FirstName, LastName, UserTypeID)
AS
(
SELECT UserID, FirstName, LastName
FROM Users
WHERE Userid = 2320
UNION ALL
SELECT np.UserID, np.FirstName, np.LastName, np.UserTypeID
AS
(
SELECT UserID, FirstName, LastName
FROM Users
WHERE Userid = 2320
UNION ALL
SELECT np.UserID, np.FirstName, np.LastName, np.UserTypeID
FROM Users AS np JOIN N ON N.UserID = np.ParentID
)
SELECT Userid, FirstName, LastName
)
SELECT Userid, FirstName, LastName
FROM N
WHERE UserTypeID = 5
ORDER BY Lastname
WHERE UserTypeID = 5
ORDER BY Lastname
Be careful with the CONNECT BY ... PRIOR statement, if in Oracle:
CONNECT BY UserID = PRIOR ParentID START WITH UserID = 2320 instead of
CONNECT BY PRIOR UserID = ParentID START WITH UserID = 2320
Then in SQL should be:
WITH N (Userid, FirstName, LastName, UserTypeID, ParentID)
AS
(
SELECT UserID, FirstName, LastName, UserTypeID, ParentID
FROM Users
WHERE Userid = 2320
UNION ALL
SELECT np.UserID, np.FirstName, np.LastName, np.UserTypeID, np.ParentID
AS
(
SELECT UserID, FirstName, LastName, UserTypeID, ParentID
FROM Users
WHERE Userid = 2320
UNION ALL
SELECT np.UserID, np.FirstName, np.LastName, np.UserTypeID, np.ParentID
FROM Users AS np JOIN N ON np.UserID = N.ParentID
)
)
SELECT Userid, FirstName, LastName
FROM N
WHERE UserTypeID = 5
ORDER BY Lastname
WHERE UserTypeID = 5
ORDER BY Lastname
For more info about using recursive queries with WITH, please refer to here.
SELECT LTRIM(SYS_CONNECT_BY_PATH(myvalues, ', '),', ') catvalues
ReplyDeleteinto v_nomineeinfo
FROM (SELECT myvalues, ROW_NUMBER() OVER (ORDER BY myvalues) rn, COUNT(*) OVER () cnt
FROM (
--SELECT NOMINEE_ID myvalues
--FROM TBL_RNR_NOMINEES
select myvalues
from
(
select
(name_complete || '(' || eid || ')') myvalues
from HAIL_ESS_USERS_EXTENDED_V
where personid in (
select NOMINEE_ID
from TBL_RNR_NOMINEES
where NOMINATIONID = p_nominationID
)
)
)
) data
WHERE rn = cnt
START WITH rn = 1
CONNECT BY PRIOR rn = rn-1;
how to migrate this code
Can you please help me out with below connect and start with oracle query to convert in to SQL ?
ReplyDeleteselect Distinct level T1.a1, T1.a2, T1.a3, T2.b1, T2.b2, T2.b3, T2.b4
from T1 TABLE1, T2 TABLE2
WHERE T1.a2=T2.a2
Start with T1.a2 IN (Select a2 from TABLE1 where a1 IS NULL)
CONNECT BY T1.a1 = PRIOR T1.a2 ORDER BY level
Can someone please help in converting the below oracle query to SQL server
ReplyDeleteselect orgidsid, ltrim(sys_connect_by_path(categorynm,'/'),'/') as path
from fccategory
where orgidsid is not null
start with REPTTOFCCATIDSID is null
connect by prior fccatidsid = repttofccatidsid and sysdate between effectivedtm and expirationdtm
order siblings by categorynm
select orgidsid, ltrim(sys_connect_by_path(categorynm,'/'),'/') as path
ReplyDeletefrom fccategory
where orgidsid is not null
start with REPTTOFCCATIDSID is null
connect by prior fccatidsid = repttofccatidsid and sysdate between effectivedtm and expirationdtm
order siblings by categorynm
Can anyone help me to convert below Oracle code into SQL
ReplyDeleteSELECT SYS_CONNECT_BY_PATH (r5rep.repgetdesc('EN','PART',epa_part,NULL,NULL) , ',')
FROM (SELECT epa_part , ROW_NUMBER () OVER (ORDER BY epa_part ) rn, COUNT (*) OVER () cnt
FROM r5entityparts left outer join r5parts on epa_part =par_code where epa_code =('3ABL09C0001' || '#*')
and par_class = 'OSSW' and par_category ='OPERATING SYSTEM')
WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;