I am trying to interpret Oracle script into SQL script and I see that I am converting the joins wrong as Oracle is using multiple instances of the same table(see orguserfield_c, orguserfield_e, etc in select statment). Could anyone please help me in converting the Oracle into SQL script. Thanks ORACLE SCRIPT:
select distinct
wh_acctcommon.effdate
, wh_acctcommon.acctnbr
, wh_acctcommon.acctclosecurrmonthyn
, wh_acctcommon.acctofficer
, wh_acctcommon.acctofficernbr
, wh_acctcommon.acctopencurrmonthyn
, wh_acctcommon.notebal
, wh_acctcommon.branchname
, wh_acctcommon.branchorgnbr
, orguserfield_c.value branch_internal_no
, orguserfield_e.value branch_status
, orguserfield_h.value branch_hub_no
, orguserfield_i.value metro_micro
, orguserfield_j.value division
, userfieldvalue_l.userfieldvaluedesc division_name
, orguserfield.value region
, userfieldvalue.userfieldvaluedesc region_name
, wh_acctcommon.primaryownercity
, wh_acctcommon.closedate
, wh_acctcommon.compoundcalpercd
, wh_acctcommon.contractdate
, wh_acctcommon.datelastmaint
, wh_acctcommon.ownername
, wh_acctcommon.bankorgnbr
, wh_acctcommon.intbase
, wh_acctcommon.intmethcd
, wh_acctcommon.noteintrate
, wh_acctcommon.ownersortname
, wh_acctcommon.loanofficer
, wh_acctcommon.loanofficersnbr
, wh_acctcommon.mjaccttypcd
, wh_acctcommon.managingofficer
, wh_acctcommon.managingofficernbr
, acctacctrolepers.persnbr SBB_Portfolio_Mgr_Nbr
, persview.fullname SBB_Portfolio_Mgr
, acctacctrolepers_c.persnbr Orig_Loan_Officer_Nbr
, persview_d.fullname Orig_Loan_Officer
, acctacctrolepers_e.persnbr Collection_Officer_Nbr
, persview_e.fullname Collection_Officer
, wh_acctcommon.datemat
, wh_acctcommon.intminbalamt
, wh_acctcommon.intmincalcbaltypcd
, wh_acctcommon.monthendyn
, wh_acctcommon.notemtdavgbal
, wh_acctcommon.nameaddr1
, wh_acctcommon.nameaddr2
, wh_acctcommon.nameaddr3
, wh_acctcommon.nameaddr4
, wh_acctcommon.nameaddr5
, wh_acctcommon.notenextratechangedate
, wh_acctcommon.noteopenamt
, wh_acctcommon.originatingperson
, wh_acctcommon.origpersnbr
, wh_acctcommon.bookbalance
, wh_acctcommon.businessphone
, wh_acctcommon.homephone
, wh_acctcommon.currmiaccttypcd
, wh_acctcommon.product
, wh_acctcommon.calcbaltypcd
, wh_acctcommon.noteratechangecalpercd
, wh_acctcommon.daysmethcd
, wh_acctcommon.noteintcalcschednbr
, wh_acctcommon.ratetypcd
, wh_acctcommon.primaryownerstate
, wh_acctcommon.curracctstatcd
, wh_acctcommon.curracctstateffdate
, wh_acctcommon.taxidnbr
, wh_acctcommon.taxrptfororgnbr
, wh_acctcommon.taxrptforpersnbr
, wh_acctcommon.currterm
, wh_acctcommon.primaryownerzipcd
, wh_acctcommon.primaryownerzipcdsuff
from OSIBANK.wh_acctcommon
, OSIBANK.orguserfield
, OSIBANK.orguserfield orguserfield_c
, OSIBANK.orguserfield orguserfield_e
, OSIBANK.orguserfield orguserfield_h
, OSIBANK.orguserfield orguserfield_i
, OSIBANK.orguserfield orguserfield_j
, OSIBANK.userfieldvalue
, OSIBANK.userfieldvalue userfieldvalue_l
, OSIBANK.acctacctrolepers
, OSIBANK.persview
, OSIBANK.acctacctrolepers acctacctrolepers_c
, OSIBANK.persview persview_d
, OSIBANK.acctacctrolepers acctacctrolepers_e
, OSIBANK.persview persview_e
where ( wh_acctcommon.branchorgnbr = orguserfield.orgnbr(+)
and upper(orguserfield.userfieldcd(+)) = 'WREG'
and orguserfield.userfieldcd = userfieldvalue.userfieldcd(+)
and orguserfield.value = userfieldvalue.userfieldvalue(+)
)
and ( wh_acctcommon.branchorgnbr = orguserfield_c.orgnbr(+)
and upper(orguserfield_c.userfieldcd(+)) = 'WBRN'
)
and ( wh_acctcommon.branchorgnbr = orguserfield_e.orgnbr(+)
and upper(orguserfield_e.userfieldcd(+)) = 'WBRS'
)
and ( wh_acctcommon.branchorgnbr = orguserfield_h.orgnbr(+)
and upper(orguserfield_h.userfieldcd(+)) = 'WHBN'
)
and ( wh_acctcommon.branchorgnbr = orguserfield_i.orgnbr(+)
and upper(orguserfield_i.userfieldcd(+)) = 'WSIZ'
)
and ( wh_acctcommon.branchorgnbr = orguserfield_j.orgnbr(+)
and upper(orguserfield_j.userfieldcd(+)) = 'WDIV'
and orguserfield_j.userfieldcd = userfieldvalue_l.userfieldcd(+)
and orguserfield_j.value = userfieldvalue_l.userfieldvalue(+)
)
and ( wh_acctcommon.acctnbr = acctacctrolepers.acctnbr(+)
and upper(acctacctrolepers.acctrolecd(+)) = 'PMGR'
and acctacctrolepers.persnbr = persview.persnbr(+)
)
and ( wh_acctcommon.acctnbr = acctacctrolepers_c.acctnbr(+)
and acctacctrolepers_c.acctrolecd(+) = 'OLOF'
and acctacctrolepers_c.persnbr = persview_d.persnbr(+)
)
and ( wh_acctcommon.acctnbr = acctacctrolepers_e.acctnbr(+)
and acctacctrolepers_e.acctrolecd(+) = 'COFF'
and acctacctrolepers_e.persnbr = persview_e.persnbr(+)
);
SQL SCRIPT:
select
wh_acctcommon.[EFFDATE]
, wh_acctcommon.acctnbr
, wh_acctcommon.acctclosecurrmonthyn
, wh_acctcommon.acctofficer
, wh_acctcommon.acctofficernbr
, wh_acctcommon.acctopencurrmonthyn
, wh_acctcommon.notebal
, wh_acctcommon.branchname
, wh_acctcommon.branchorgnbr
,OUF.value AS 'branch_internal_no'
, OUF.value AS 'branch_status'
, OUF.value AS 'branch_hub_no'
, OUF.value AS 'metro_micro'
, OUF.value AS 'division'
, UFV.[USERFIELDVALUEDESC] AS 'division_name'
, OUF.value AS 'region'
, UFV.userfieldvaluedesc AS 'region_name'
, wh_acctcommon.primaryownercity
, wh_acctcommon.closedate
, wh_acctcommon.compoundcalpercd
, wh_acctcommon.contractdate
, wh_acctcommon.datelastmaint
, wh_acctcommon.ownername
, wh_acctcommon.bankorgnbr
, wh_acctcommon.intbase
, wh_acctcommon.intmethcd
, wh_acctcommon.noteintrate
, wh_acctcommon.ownersortname
, wh_acctcommon.loanofficer
, wh_acctcommon.loanofficersnbr
, wh_acctcommon.mjaccttypcd
, wh_acctcommon.managingofficer
, wh_acctcommon.managingofficernbr
, AARP.persnbr AS 'SBB_Portfolio_Mgr_Nbr'
, PV.fullname as 'SBB_Portfolio_Mgr'
, AARP.persnbr AS 'Orig_Loan_Officer_Nbr'
, PV.fullname AS 'Orig_Loan_Officer'
, AARP.persnbr as 'Collection_Officer_Nbr'
, PV.fullname AS 'Collection_Officer'
, wh_acctcommon.datemat
, wh_acctcommon.intminbalamt
, wh_acctcommon.intmincalcbaltypcd
, wh_acctcommon.monthendyn
, wh_acctcommon.notemtdavgbal
, wh_acctcommon.nameaddr1
, wh_acctcommon.nameaddr2
, wh_acctcommon.nameaddr3
, wh_acctcommon.nameaddr4
, wh_acctcommon.nameaddr5
, wh_acctcommon.notenextratechangedate
, wh_acctcommon.noteopenamt
, wh_acctcommon.originatingperson
, wh_acctcommon.origpersnbr
, wh_acctcommon.bookbalance
, wh_acctcommon.businessphone
, wh_acctcommon.homephone
, wh_acctcommon.currmiaccttypcd
, wh_acctcommon.product
, wh_acctcommon.calcbaltypcd
, wh_acctcommon.noteratechangecalpercd
, wh_acctcommon.daysmethcd
, wh_acctcommon.noteintcalcschednbr
, wh_acctcommon.ratetypcd
, wh_acctcommon.primaryownerstate
, wh_acctcommon.curracctstatcd
, wh_acctcommon.curracctstateffdate
, wh_acctcommon.taxidnbr
, wh_acctcommon.taxrptfororgnbr
, wh_acctcommon.taxrptforpersnbr
, wh_acctcommon.currterm
, wh_acctcommon.primaryownerzipcd
, wh_acctcommon.primaryownerzipcdsuff
FROM
[DNA_Staging].[dbo].[WH_ACCTCOMMON] wh_acctcommon
LEFT OUTER JOIN [DNA_Staging].[dbo].orguserfield OUF
ON wh_acctcommon.branchorgnbr = OUF .orgnbr
and upper(OUF.userfieldcd) IN( 'WREG','WBRN','WBRS','WHBN','WSIZ','WDIV')
LEFT OUTER JOIN [DNA_Staging].[dbo].userfieldvalue UFV
ON OUF.userfieldcd = UFV.userfieldcd
and OUF.value = UFV.userfieldvalue
LEFT OUTER JOIN [DNA_Staging].[dbo].[ACCTACCTROLEPERS] AARP
ON wh_acctcommon.acctnbr = AARP.acctnbr
and upper(AARP.acctrolecd) IN ('PMGR','OLOF','COFF')
LEFT OUTER JOIN [DNA_Cleanup_DM].[dbo].PERSVIEW PV
ON AARP.persnbr = PV.persnbr
