• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Left Outer Join

I'm doing my first Left Outer Join in E1.

iSeries V6R1M0
E900, Tools Release 8.98.43

Screen shots of the join and data selection attached (hopefully).

Can anyone tell me why the generated SQL that I see in the debuglog also selects IBMCU = NULL?? I definitely don't want that and I'm just not getting this one.

FROM DVBDTA/F4101 T0 LEFT OUTER JOIN DVBDTA/F4102 T1
ON T0.IMITM = T1.IBITM
WHERE ( ( T0.IMGLPT IN ( 'AG01' ) OR ( T1.IBMCU = ' 6009000' OR T1.IBMCU IS NULL ) ) )
 

Attachments

BOster

Member
Unfortunately there is no [standard] way to avoid it, at least none that I have found. The JDEBASE engine will allways add the ..."OR is null" clause.

If you will always be searching on the right side of the Join, in your example T1, then you could consider just doing a simple inner join instead. If you want to look for unmatched records or display unmatched records or your query may or may not have data selection on the right side of the join you would have to resort to doing something like a virtual table (SQL View) and do the join at the SQL view level and then cast or convert the null values for unmatched records to zeros and blanks, etc.
 
I think you want to specify a SQL 92 Outer join in the business view. If I remember right, that won't add the OR NULL for each right side criterion.
 
T., I've used it a couple of times. And checked the generated select statement. It works.

Check the following doc for more details:

SOLUTION ID: 200821061: E1: TDA: Null addition with a left outer join business view

[ QUOTE ]
Starting with EnterpriseOne 8.9, a new join type "SQL 92 Left Outer" is available in Business View Design Aid. This new join type allows the user to define a left outer join business view where any data selection on a column in the TO table will not have the 'Or Column = NULL' clause appended.

[/ QUOTE ]
 

p_watton

Member
Hi,



If you are trying to return only the matching records in the primary
table when there is filter/selection in the secondary table then you
could



Add the additional filter based on the join key TO.IMTM =3D T0.IBTM .
This will de-select the unmatched primary records returned by the 'OR
T1.IBMCU is null' selection



Cheers





Paul



From: jdeadmin@server.host53.com [mailto:jdeadmin@server.host53.com] On
Behalf Of T. McQuinn
Sent: 18 September 2012 19:50
To: jdelistsubscribers@jdelist.com
Subject: Left Outer Join



I'm doing my first Left Outer Join in E1.

iSeries V6R1M0
E900, Tools Release 8.98.43

Screen shots of the join and data selection attached (hopefully).

Can anyone tell me why the generated SQL that I see in the debuglog also
selects IBMCU =3D NULL?? I definitely don't want that and I'm just not
getting this one.

FROM DVBDTA/F4101 T0 LEFT OUTER JOIN DVBDTA/F4102 T1
ON T0.IMITM =3D T1.IBITM
WHERE ( ( T0.IMGLPT IN ( 'AG01' ) OR ( T1.IBMCU =3D ' 6009000' OR T1.IBMCU
IS NULL ) ) )

E900, 8.98.33, iSeries @ V6R1M0
 
Top