Problem: I am on Progress 9.1D and I cannot build a query that gets me the information I need. Progress seems to only allow serial joins. Meaning, I must singly join each table one at a time; one before the other.
I need the tables involved in Joins 2 and 3 to be cojoin before I join it to the rest of the query. Why Join 2 (pub.shipdtl sd) contains the Jobnumber that links it back to the rest of the query, but Join 3 (pub.shiphead sh) holds the shipment status. I am trying to avoid selecting voided shipments, but I am unable to do so because of the way Progress processes the joins.
I was hoping to join pub.shiphead sh to pub.shipdtl sd and then select only the shipments that have not been voided, and then join the resultset to the rest of the query, but I don't know how to do this with Progress.
Please Help!
Actual Query:
select jo.jobnum
on ( jo.jobnum = jh.jobnum
left join pub.shipdtl sd
on (
left join pub.shiphead sh
on (
left join pub.customer c
on(
) --4
I need the tables involved in Joins 2 and 3 to be cojoin before I join it to the rest of the query. Why Join 2 (pub.shipdtl sd) contains the Jobnumber that links it back to the rest of the query, but Join 3 (pub.shiphead sh) holds the shipment status. I am trying to avoid selecting voided shipments, but I am unable to do so because of the way Progress processes the joins.
I was hoping to join pub.shiphead sh to pub.shipdtl sd and then select only the shipments that have not been voided, and then join the resultset to the rest of the query, but I don't know how to do this with Progress.
Please Help!
Actual Query:
select jo.jobnum
,cast(jh.duedate as varchar(25)) as duedate
,jo.oprseq
,jo.description
--,jo.duedate as OpDue
,jh.partnum
,jh.revisionnum
,case when jh.jobclosed = 1 then 'JobClosed'
when (jh.jobcomplete = 1 and jh.jobclosed = 0) then 'JobComplete'
end as JobStatus
,sd.packnum
,sd.packline
,case when sd.shipcmpl = 1 then 'Shipped'
when (sd.shipcmpl = 0) then 'NotYetShipped'
end as ShipStatus
,case when sd.shipcmpl = 1 then cast(sh.shipdate as varchar(25))
end as ShipDate
,cast(sd.ourinventoryshipqty+ourjobshipqty as varchar(25)) as shipQty
,case when sh.invoiced = 1 then 'Invoiced'
when (sh.invoiced = 0) then 'NotInvoiced'
end as InvoiceStatus
,sd.ordernum
,sd.orderline
,sd.orderrelnum
,sh.custnum
,c.name
from ((((pub.joboper jo inner join pub.jobhead jh,jo.oprseq
,jo.description
--,jo.duedate as OpDue
,jh.partnum
,jh.revisionnum
,case when jh.jobclosed = 1 then 'JobClosed'
when (jh.jobcomplete = 1 and jh.jobclosed = 0) then 'JobComplete'
end as JobStatus
,sd.packnum
,sd.packline
,case when sd.shipcmpl = 1 then 'Shipped'
when (sd.shipcmpl = 0) then 'NotYetShipped'
end as ShipStatus
,case when sd.shipcmpl = 1 then cast(sh.shipdate as varchar(25))
end as ShipDate
,cast(sd.ourinventoryshipqty+ourjobshipqty as varchar(25)) as shipQty
,case when sh.invoiced = 1 then 'Invoiced'
when (sh.invoiced = 0) then 'NotInvoiced'
end as InvoiceStatus
,sd.ordernum
,sd.orderline
,sd.orderrelnum
,sh.custnum
,c.name
on ( jo.jobnum = jh.jobnum
and jo.company = jh.company
and jo.company = 'ACME'
and jo.opcomplete = 0
and (jh.jobclosed = 1 or jh.jobcomplete = 1) and jh.duedate between to_date('12/1/2008') and to_date('1/7/2009') )
) --1and jo.company = 'ACME'
and jo.opcomplete = 0
and (jh.jobclosed = 1 or jh.jobcomplete = 1) and jh.duedate between to_date('12/1/2008') and to_date('1/7/2009') )
left join pub.shipdtl sd
on (
sd.jobnum = jh.jobnum
and sd.company = jh.company )
) --2and sd.company = jh.company )
left join pub.shiphead sh
on (
sd.company = sh.company
and sd.ordernum = sh.OrderNum
and sd.packnum = sh.packnum
and sd.custnum = sh.custnum
and sh.voided = 0 )
) --3and sd.ordernum = sh.OrderNum
and sd.packnum = sh.packnum
and sd.custnum = sh.custnum
and sh.voided = 0 )
left join pub.customer c
on(
sh.custnum = c.custnum )
) --4