Hello,
I have this query that is supposed to be a Left Outer Join, but it is producing a Cartesian product. Is anyone able to help? Perhaps you can execute this in your own test environment and see if you come up with the same result.
Database: PROGRESS Version 9.1D0920 as of Tue Oct 19 20:04:28 EDT 2004
note: We have not gone through with a DB upgrade. Our software provider has not tested the ERP system with 9.1E or greater.
SELECT * From
(select jm.jobnum as jnum
(
select pr.jobnum
on job.jnum = hh.jobnum
and job.mtlpart = hh.partnum
and job.assseq = hh.assemblyseq
and job.comp = hh.company
I have this query that is supposed to be a Left Outer Join, but it is producing a Cartesian product. Is anyone able to help? Perhaps you can execute this in your own test environment and see if you come up with the same result.
Database: PROGRESS Version 9.1D0920 as of Tue Oct 19 20:04:28 EDT 2004
note: We have not gone through with a DB upgrade. Our software provider has not tested the ERP system with 9.1E or greater.
SELECT * From
(select jm.jobnum as jnum
,jm.partnum as MtlPart
,jm.assemblyseq as assseq
,jm.company as comp
,jh.partnum as FGDSPart
,cast(jm.requiredqty as varchar(25)) as requiredqty
,cast(jm.issuedqty as varchar(25)) as issuedqty
,case
when jm.issuedComplete = 0 then 'no'
when jm.issuedcomplete = 1 then 'yes'
end as issuedCompleted
, cast(jm.requiredqty - jm.issuedqty as varchar(25)) as toBeIssued
,cast(jm.reqDate as varchar(25)) as reqDate
,'' as qtyOnhand
,'' as qtyShort
,jm.revisionnum as MtlRev
,'---******---' as div
from pub.jobhead jh
,pub.jobmtl jm
,pub.jobasmbl ja
where jh.company = 'ACME'
and jh.duedate is not null
and jh.jobclosed = 0
and jh.jobcompletionDate is null
and jh.partnum <> ''
and jh.jobcomplete = 0
and ja.company = 'ACME'
and jh.company = ja.company
and jh.jobnum = ja.JobNum
and jh.partnum = ja.partnum
and jm.company = 'ACME'
and jm.company = ja.company
and jm.jobnum = ja.jobnum
and jm.partnum <> ''
and jm.jobnum = ja.jobnum
and jm.AssemblySeq = jm.assemblyseq
and jm.mtlseq >= 0
and jm.jobcomplete = 0
and jm.requiredqty - jm.issuedqty > 0
and jh.duedate between to_date('10/28/2008') and to_date('11/19/2008')
) as job LEFT OUTER JOIN,jm.assemblyseq as assseq
,jm.company as comp
,jh.partnum as FGDSPart
,cast(jm.requiredqty as varchar(25)) as requiredqty
,cast(jm.issuedqty as varchar(25)) as issuedqty
,case
when jm.issuedComplete = 0 then 'no'
when jm.issuedcomplete = 1 then 'yes'
end as issuedCompleted
, cast(jm.requiredqty - jm.issuedqty as varchar(25)) as toBeIssued
,cast(jm.reqDate as varchar(25)) as reqDate
,'' as qtyOnhand
,'' as qtyShort
,jm.revisionnum as MtlRev
,'---******---' as div
from pub.jobhead jh
,pub.jobmtl jm
,pub.jobasmbl ja
where jh.company = 'ACME'
and jh.duedate is not null
and jh.jobclosed = 0
and jh.jobcompletionDate is null
and jh.partnum <> ''
and jh.jobcomplete = 0
and ja.company = 'ACME'
and jh.company = ja.company
and jh.jobnum = ja.JobNum
and jh.partnum = ja.partnum
and jm.company = 'ACME'
and jm.company = ja.company
and jm.jobnum = ja.jobnum
and jm.partnum <> ''
and jm.jobnum = ja.jobnum
and jm.AssemblySeq = jm.assemblyseq
and jm.mtlseq >= 0
and jm.jobcomplete = 0
and jm.requiredqty - jm.issuedqty > 0
and jh.duedate between to_date('10/28/2008') and to_date('11/19/2008')
(
select pr.jobnum
,pd.partnum
,pr.assemblyseq
, pd.company
, pd.revisionnum
,pr.duedate as "Due_Date"
,pr.relqty as DueQty
,'' as vendor
,pr.relqty as relqty
,pr.receivedqty as recvqty
,pr.ponum as ponum
,pr.poline as poline
,pr.porelnum as porelnum
,pr.jobseq
from pub.porel pr inner join PUB.PODetail pd
ON (pr.company = pd.company
and pr.ponum = pd.ponum
and pr.poline = pd.poline
and pr.company = 'ACME'
)
)as hh,pr.assemblyseq
, pd.company
, pd.revisionnum
,pr.duedate as "Due_Date"
,pr.relqty as DueQty
,'' as vendor
,pr.relqty as relqty
,pr.receivedqty as recvqty
,pr.ponum as ponum
,pr.poline as poline
,pr.porelnum as porelnum
,pr.jobseq
from pub.porel pr inner join PUB.PODetail pd
ON (pr.company = pd.company
and pr.ponum = pd.ponum
and pr.poline = pd.poline
and pr.company = 'ACME'
)
on job.jnum = hh.jobnum
and job.mtlpart = hh.partnum
and job.assseq = hh.assemblyseq
and job.comp = hh.company