Rewrite this two seperate queries using the new methods – joins (left join where necessary )
Select
a.APP_UNIT_CENTER as “Budget Center”,
a.PARENT as “Parent WO”,
par.STATUS
TO_CHAR(a.STATUSDATE, ‘MM/DD/YYYY’) as “PWO status Date”,
to_CHAR(a.TARGCOMPDATE, ‘MM/DD/YYYY’) as “PWO Target Finish Date”,
SUBSTR(a.GLACCOUNT, 27, 44 ) as “GL Account”,
a.WORKTYPE as “Work Type”,
a.APP_SUBTYPE as “Sub Type”,
a.APP_STRUCTURETYPE as “Structure Type”,
to_char(a.WORKLOCATION) as “Assigned Labor”,
a.APP_CUSTNAME as “Customer Name”,
a.APP_ADDSTRTNUM as “Street Number”,
a.APP_ADDSTREET as “Street Name”,
a.APP_ADDCITY as “City”,
a.APP_ADDZIP as “Zipcode”,
a.APP_ADDCOMBO as “Full Address”,
SUBSTR(a.APP_CUSTPHONEPRIM, 1, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 4, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 7, 4) as “Primary Phone”,
par.REPORTEDBY “Reported by”,
to_char(par.REPORTDATE, ‘MM/DD/YYYY’) as “Reported Date”,
a.REPORTEDBY “JP Reported by”,
to_char(a.REPORTDATE, ‘MM/DD/YYYY’) as “JP Reported Date”,
u.WONUM as “Office Analysis Task WO”,
u.STATUS as “Analysis Task Status“,
to_char(u.STATUSDATE, ‘MM/DD/YYYY’) as “Analysis Status Date”,
b.WONUM as “Investigate Task WO”,
b.STATUS as “Investigate Task Status”,
to_char(b.STATUSDATE, ‘MM/DD/YYYY’) as “Investigate Status Date”,
o.WONUM as “Complete OPA Task WO”,
o.STATUS as “OPA Task Status”,
to_char(o.STATUSDATE, ‘MM/DD/YYYY’) as “OPA Status Date”,
v.WONUM as “Engineering Review Task WO”,
v.STATUS as “Engineering Review Task Status”,
to_char(v.STATUSDATE, ‘MM/DD/YYYY’) as “Engineering Review Date“,
p.WONUM as “Obtain Permit Task WO”,
p.STATUS as “Permit Task Status“,
to_char(p.STATUSDATE, ‘MM/DD/YYYY’) as “Permit Status Date”,
c.WONUM as “Prepare Quote for Cust Task WO“,
c.STATUS as “Quote Task Status”,
to_char(c.STATUSDATE, ‘MM/DD/YYYY’) as “Quote Status Date”,
d.WONUM as “Cust Charges Task WO”,
d.STATUS as “Cust Charges Task Status”,
to_char(d.STATUSDATE, ‘MM/DD/YYYY’) as “Cust Charges Status Date”,
e.WONUM as “Released for Const Task WO“,
e.STATUS as “Released for Const Task Status”,
to_char(e.STATUSDATE, ‘MM/DD/YYYY’) as “Released for Const Status Date”,
f.WONUM as “Manifold Task WO”,
f.STATUS as “Manifold Task Status”,
to_char(f.STATUSDATE, ‘MM/DD/YYYY’) as “Manifold Status Date”,
a.WONUM as “CONS Task WO”,
a.STATUS as “CONS Task Status”,
to_char(a.STATUSDATE, ‘MM/DD/YYYY’) as “CONS Status Date”,
a.ACTFINISH as “CONS Actual Finish”,
i.status as “Work Order Status History”,
to_char(i.CHANGEDATE, ‘MM/DD/YYYY’) as “WO History Status Date”,
g.ALNVALUE as “Field Completion Date”,
SUBSTR (A.APP_EXTERNALID, 1, 13) as “CRM/CSB ID”,
a.APP_EXTERNALID as “Premise ID”,
a.APP_EVENTSUBTYPE as “Mat Code”,
a.APP_CUSTTYPE,
a.APP_WORKPLANBY as “Work Planned by ID”,
n.DISPLAYNAME as “Work Planned by Name”,
a.OWNER as “Supervisor”,
nn.DISPLAYNAME as “Owner Name”,
j.JPNUM as “JobPlan”
from workorder a,
workorder par,
(select * from workorder where APP_TASKTYPE =’INVEST’) b,
(select * from workorder where APP_TASKTYPE =’QUOTE’) c,
(select * from workorder where APP_TASKTYPE =’CC’) d,
(select * from workorder where APP_TASKTYPE =’RELEASED’) e,
(select * from workorder where APP_TASKTYPE =’OPA’) o,
(select * from workorder where APP_TASKTYPE =’PERMIT’) p,
(select * from workorder where APP_TASKTYPE =’OFCANL’) u,
(select * from workorder where APP_TASKTYPE =’ENGREV) v,
(select * from workorder where APP_TASKTYPE =’MANDMI’) f,
workorderspec g,
jobplan j,
person n,
person nn,
wostatus i
where
a.ISTASK =’1′
and a.siteid = ‘2280’
and a.APP_TASKTYPE = ‘CONS’
and PAR.APP_UNIT_CENTER IN (‘MICSC’, ‘LYNSC’, ‘ARDCT’)
and PAR.WORKTYPE IN (‘NC’, ‘SA’)
and PAR.STATUS IN ‘WAPPR’
–and a.STATUS NOT IN (‘CLOSE’, ‘COMP’, ‘EBERROR’, ‘FDCOMP’, ‘CAN’, ‘CANDUP’, ‘FDCANDISP’, ‘FLN’, ‘CAN-COMP’, ‘RECOMP’, ‘SADMCAN’)
and trunc(par.reportdate) >= to_date(‘2020-01-01’, ‘YYYY-MM-DD’)
and trunc(par.reportdate) <= to_date(‘2025-12-31’, ‘YYYY-MM-DD’)
and g.assetattrid = ‘ASBFIELDCOMPDATE’
and a.PARENT = b.PARENT(+)
and a.PARENT = c.PARENT(+)
and a.PARENT = d.PARENT(+)
and a.PARENT = e.PARENT(+)
and a.PARENT = o.PARENT(+)
and a.PARENT = p.PARENT(+)
and a.PARENT = u.PARENT(+)
and a.PARENT = v.PARENT(+)
and a.PARENT = f.PARENT(+)
and a.PARENT = par.wonum
and a.WONUM = g.WONUM
and a.wonum = i.wonum
and n.personid(+) = a.APP_WORKPLANBY
and nn.personid(+) = a.OWNER
and par.JPNUM = j.JPNUM (+)
I.status LIKE ‘WAPPR’
order by a.Parent DESC
This is the second query below –
Select
a.APP_UNIT_CENTER as “Budget Center”,
a.wonum as “Parent WO”,
a.STATUS as PARENT WO Status”,
a.WORKTYPE,
a.APP_SUBTYPE,
a.APP_CUSTNAME as “Customer Name”,
SUBSTR(a.APP_CUSTPHONEPRIM, 1, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 4, 3) || ‘-‘||SUBSTR(a.APP_CUSTPHONEPRIM, 7, 4) as “Primary Phone”,
a.APP_ADDCOMBO as “Full Address”,
SUBSTR(A.APP_EXTERNALID, 1, 13) as “CRM/CSB ID”,
a.APP_ADDEXTERNALID as “Premise ID”,
a.APP_CUSTTYPE,
TO_CHAR(a.REPORTDATE, ‘MM/DD/YYYY’) as “Reported Date”,
TO_CHAR(a.SCHEDSTART, ‘MM/DD/YYYY’) as “Customer Appt Date”,
a.APP_WORKPLANBY as “Work Planned by ID”,
a.DISPLAYNAME as “Work Planned by Name”,
a.JPNUM
from workorder a,
jobplan j,
person n
where a.siteid = ‘2280’
and A.APP_UNIT_CENTER IN (‘MICSC’, ‘LYNSC’, ‘ARDCT’)
and a.ISTASK =’0′
and a.WORKTYPE in (‘SA’, ‘NC’)
and a.STATUS IN ‘WAPPR’
and trunc(a.reportdate) >= to_date(‘2020-01-01’, ‘YYYY-MM-DD’)
and trunc(a.reportdate) <= to_date(‘2025-12-31’, ‘YYYY-MM-DD’)
and n.personid(+) = a.APP_WORKPLANBY
and a.Parent is null
and a.JPNUM is null
and a.APP_JPCATEGORY is null
and j.DESCRIPTION is null
and a.APP_WBS4_SEGMENT is null
and a.JPNUM = j.JPNUM(+)
order by a.wonum
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.
Read moreEach paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.
Read moreThanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.
Read moreYour email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.
Read moreBy sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.
Read more
Recent Comments