December 20, 2011 at 8:03 am
This query was taken from Oracle. It uses "SEARCH DEPTH FIRST BY ID ASC SET seq#" part in the end. Can anybody explain what it does on this query or how can be written in TSQL?
Thx!
WITH rec(groupid, levelx, id, code, parent, accountname, pkg, class, groupcode, primarycontact, billable, activationdate, acc_OIB, parent_OIB) AS
(
SELECT a.id AS groupid, 1 AS levelx, a.id, a.code, a.parent, a.accountname
, a.productcode as pkg, a.class, a.groupcode, a.primarycontact, a.billable, a.activationdate
, accc.identity_no as acc_OIB, v_accc.identity_no as parent_OIB
FROM accounts a--, acctstatus acs
left outer join acctcontacts accc on a.primarycontact=accc.id and accc.identity_type in ('OIB')
left outer join accounts v_acc on a.parent=v_acc.id
left outer join acctcontacts v_accc on v_acc.primarycontact=v_accc.id and v_accc.identity_type in ('OIB')
WHERE a.parent is null
and a.class=1 -- SCD
and a.category=1 -- SCD
AND EXISTS (SELECT 1
FROM acctudf asu
WHERE a.id=asu.accountid
AND asu.fieldcode='NAV_PLAT_KAM'
AND SUBSTR(asu.value, 1, 2) IN ('ZD', 'PU', 'ZG', 'RI', 'OS', 'ST', 'WS') -- SCD
AND lower(asu.value) LIKE '%romac%'
AND asu.value NOT LIKE '%SMB%')
--AND a.id=acs.accountid
--AND acs.status='A'
--AND SYSDATE-10 BETWEEN acs.fromdate AND acs.todate
UNION ALL
SELECT
CASE WHEN accc.identity_no <> v_accc.identity_no THEN a1.id ELSE groupid END AS groupid
, levelx+1, a1.id, a1.code, a1.parent, lpad(a1.accountname, length(a1.accountname)+(levelx)*10, ' ') AS accountname
, a1.productcode as pkg, a1.class, a1.groupcode, a1.primarycontact, a1.billable, a1.activationdate
, accc.identity_no as acc_OIB, v_accc.identity_no as parent_OIB
FROM rec r, accounts a1--, acctstatus acs2
left outer join acctcontacts accc on a1.primarycontact=accc.id and accc.identity_type in ('OIB')
left outer join accounts v_acc on a1.parent=v_acc.id
left outer join acctcontacts v_accc on v_acc.primarycontact=v_accc.id and v_accc.identity_type in ('OIB')
WHERE r.id=a1.parent
--AND a1.id=acs2.accountid
--AND acs2.status='A'
--AND SYSDATE-10 BETWEEN acs2.fromdate AND acs2.todate
)
SEARCH DEPTH FIRST BY ID ASC SET seq#
December 20, 2011 at 8:29 am
First of all it’s SQL Server Central and you should not expect the answer for PL/SQL query here.
Secondly, few guys here work on multiple databases but its advanced PL/SQL (Oracle 11g).
Thirdly (& luckily) I can explain it a bit.
WITH rec: You declared a CTE here.
SEARCH DEPTH FIRST BY ID ASC SET seq#: You set the sequence number based on ID column (asc).
You copied only a portion of the code. The remaining code will build some logic to form a b-tree. In summary it’s an alternative to CONNECT_BY_ISLEAF function (recursive sub query). In SSC terms it’s RBAR (oooops what I have said here :w00t:).
For more, please visit Oracle Forums.
December 20, 2011 at 8:32 am
Thanx for effort!
We'll search the Oracle forum.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply