Left Outer Join Produces Cartersian Product. Please Help!

[ERPgal]

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

,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
(
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
on job.jnum = hh.jobnum
and job.mtlpart = hh.partnum
and job.assseq = hh.assemblyseq
and job.comp = hh.company

 

[tamhas]

Just at a quick glance my first recommendation would be to restructure the from jobhead part so that the conditions were a part of the join, i.e., do an explicit inner join with ON. I've had a lot better results with that than putting join conditions in the WHERE, although I can't point to any specific bugs.

Of course, the other thing I would do is to slice it up and try assembling it in pieces to see where it goes wrong .... tends to provide a good hint!

 

[ERPgal]

I tried your suggestion and I am still getting the same result.
Here is what I did:

Select jnum, fgdspart, mtlpart,ponum, poline from
(

select jm.jobnum as jnum
,jm.partnum as MtlPart
,jm.assemblyseq as assseq
,jm.company as comp
,jh.partnum as FGDSPart
,jm.requiredqty as requiredqty
,jm.issuedqty as issuedqty
,case
when jm.issuedComplete = 0 then 'no'
when jm.issuedcomplete = 1 then 'yes'
end as issuedCompleted
,jm.requiredqty - jm.issuedqty as toBeIssued
,jm.reqDate as reqDate
,'' as qtyOnhand
,'' as qtyShort
,jm.revisionnum as MtlRev
,'---******---' as div​

from (pub.jobhead jh inner join pub.jobasmbl ja
on

(jh.company = 'Genmech'
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 = 'Genmech'
and jh.company = ja.company
and jh.jobnum = ja.JobNum
and jh.partnum = ja.partnum
and jh.duedate between to_date('10/28/2008') and to_date('11/19/2008')
)​

) inner join pub.jobmtl jm

on
( jm.company = ja.company
and jm.jobnum = ja.jobnum
and jm.company = 'Genmech'
and jm.partnum <> ''
and jm.AssemblySeq = jm.assemblyseq
and jm.mtlseq >= 0
and jm.jobcomplete = 0
and jm.requiredqty - jm.issuedqty > 0
)​

) as job LEFT JOIN
(
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 = 'Genmech'
)
​

)as hh
on (job.jnum = hh.jobnum
and job.mtlpart = hh.partnum
and job.assseq = hh.assemblyseq
and job.comp = hh.company)
​

 

[tamhas]

OK, but have you tried cutting down to a minimal query and adding pieces progressively to find where it goes cartesian? Not only is that the strategy I would use, but even if you don't figure it out, it should allow you to post a very specific query about why a particular addition has that effect. Viewed as a whole it is rather a lot to try to take in.

 

[ERPgal]

OK, but have you tried cutting down to a minimal query and adding pieces progressively to find where it goes cartesian? Not only is that the strategy I would use, but even if you don't figure it out, it should allow you to post a very specific query about why a particular addition has that effect. Viewed as a whole it is rather a lot to try to take in.

Good suggestion. I took it a part and it looks as if no matter what I do, and no matter how many tables I include/exclude, the DB cannot do a smart join. It's only doing a Cross Join / Cartesian Product join with the two result sets.

tamhas, have you successfully executed a similar query in Progress?

 

[tamhas]

I've done some that were a lot longer and more complex, in fact, but often with a certain amount of pain. I only resort to SQL for reporting tools. I haven't studied your query in detail, but it looks like the sort of thing one could do in ABL with no problem and quickly and predictably, whereas one has to tie oneself in knots to join all that stuff in SQL without it blowing up.

 

[ERPgal]

Tamhas, this query will be used in an ASP.NET application. What is ABL ?

 

[ERPgal]

Will you copy and paste an example of a similar query that you were able to execute? That would really help me to see what I am doing wrong with SQL92.

 

[ERPgal]

I found the solution. Progress does not seem to like working with Resultsets / temporary tables. Instead it is best to build the query serially using a sequence of joins. Basically, you are tacking on one table at a time.


Here is what I ended up with:

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
, cast(pr.ponum as varchar(25)) as ponum
, cast(pr.poline as varchar(25)) as poline
, cast(pr.porelnum as varchar(25)) as porelnum
, cast(pr.duedate as varchar(25)) as "Due_Date"
, cast(pr.relqty as varchar(25)) as DueQty
,'' as vendor
, cast(pr.relqty as varchar(25)) as relqty
,pr.receivedqty as recvqty
,pr.jobseq
,v.vendorid​

from ((((pub.jobhead jh inner join pub.jobasmbl ja

, on
(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 jh.duedate between to_date('10/28/2008') and to_date('11/19/2008')
)
)​

inner join pub.jobmtl jm

on
( jm.company = ja.company
and jm.jobnum = ja.jobnum
and jm.company = 'ACME'
and jm.partnum <> ''
and jm.AssemblySeq = ja.assemblyseq
and jm.mtlseq >= 0
and jm.jobcomplete = 0
and jm.requiredqty - jm.issuedqty > 0
)​

) left join pub.porel pr

on jh.jobnum = pr.jobnum
and jm.assemblyseq = pr.assemblyseq
and jh.company = pr.company
​

) left outer join pub.podetail pd

on pr.ponum = pd.ponum
and pr.poline = pd.poline
and jm.partnum = pd.partnum
and pr.company = pd.company
​

) left join pub.vendor v
on pd.vendornum = v.vendornum
and pd.company = v.company

 

[tamhas]

ABL = Advanced Business Language, formerly known as Progress 4GL, i.e., the native language of OpenEdge, aka Progress, applications

 

[tamhas]

Progress does not seem to like working with Resultsets / temporary tables. Instead it is best to build the query serially using a sequence of joins

Yup ... another good rule when using SQL against an OpenEdge database.

 

[tamhas]

BTW, while I have your attention, 9.1D is a very old version. If there is some really, really compelling reason to stay on 9.x, then you should be on 9.1E with the latest service pack ... oh, I just realized that you are the same person that Tom Bascom has been telling this to on the other thread. Well, let me also note that each release of Progress has seen a significant improvement in SQL. Reasonably current versions have type 4 drivers. Another really good reason to upgrade.

 

[ERPgal]

BTW, while I have your attention, 9.1D is a very old version. If there is some really, really compelling reason to stay on 9.x, then you should be on 9.1E with the latest service pack ... oh, I just realized that you are the same person that Tom Bascom has been telling this to on the other thread. Well, let me also note that each release of Progress has seen a significant improvement in SQL. Reasonably current versions have type 4 drivers. Another really good reason to upgrade.

We are running Vantage 6.1.542 which runs on 9.1D and haven't gone through an ERP upgrade. We are contemplating whether or not an upgrade is the best move for our company. Until then we are stuck with 9.1D

 

[tamhas]

As Tom points out, 99.99% of this refusal to let people upgrade the Progress while staying on the older version of the software is simply self-interested nonsense. The only reason they have for refusing is that they don't want to take the effort to do any testing on anything but their current version and they don't want to have to support you with an unknown. But, in point of fact, the likelihood of having trouble from this source is minimal and 99.99% of any likely trouble is something easily discovered by you the customer during a testing phase, i.e., compile it on a new version and try it out. If it compiles and you figure out any needed parameter changes, you are over most of the risks.

Frankly, I think it is unconscionable for a vendor to keep you from going to a supported release. If anything were to go wrong with your current installation and you went to Progress, the first thing they would tell you to do was upgrade.

 

[ERPgal]

As Tom points out, 99.99% of this refusal to let people upgrade the Progress while staying on the older version of the software is simply self-interested nonsense. The only reason they have for refusing is that they don't want to take the effort to do any testing on anything but their current version and they don't want to have to support you with an unknown. But, in point of fact, the likelihood of having trouble from this source is minimal and 99.99% of any likely trouble is something easily discovered by you the customer during a testing phase, i.e., compile it on a new version and try it out. If it compiles and you figure out any needed parameter changes, you are over most of the risks.

Frankly, I think it is unconscionable for a vendor to keep you from going to a supported release. If anything were to go wrong with your current installation and you went to Progress, the first thing they would tell you to do was upgrade.

You make a good point Tamhas, but it turns out that in stead of telling us to upgrade progress, they are telling us to Upgrade our ERP system ...go figure. It is still a supported system, but most of their new employees have more expertise with their most recent version of the software.

I admit, we are in a tough spot and we need to make a decision soon.
Hey, thanks for all the attention you have given me in both these threads. Your feedback has surely helped.

 

[tamhas]

Unless there is some barrier to upgrade of the ERP software, I am generally in favor of that too. In fact, as an AP I do continuous upgrades of customers so that they never fall behind, even if they have custom code.