Need Help With Tricky Join Query

[ERPgal]

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

,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
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') )​

) --1

left join pub.shipdtl sd
on (

sd.jobnum = jh.jobnum
and sd.company = jh.company )​

) --2
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 )​

) --3
left join pub.customer c
on(

sh.custnum = c.custnum )​

) --4

 

[ERPgal]

Found what I was doing wrong. I needed add a WHERE clause at the very end of the query, outside of the parenthesis.


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
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') )
​

) --1

left join pub.shipdtl sd
on (

sd.jobnum = jh.jobnum
​

and sd.company = jh.company )
​

) --2
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
)
​

) --3
left join pub.customer c
on(

sh.custnum = c.custnum )
​

) --4

WHERE sh.voided = 0