Left Outer Join

[T. McQuinn]

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

 

[BOster]

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.

 

[craig_welton]

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.

 

[Remo Williams]

Yep, Craig is right: SQL92 Left Outer is the one you need. Available from EnterpriseOne 8.9 upwards.

 

[T. McQuinn]

I looked at SQL 92 left outer but it sounded like I would still get the nulls, just by definition rather than selection.

http://tinyurl.com/8gdozyl

 

[lmujica]

Hello McQuinn,

Craig and Remo're right. Check DOC ID 663779.1 on MOS.

Regards,



Luis.

 

[Remo Williams]

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]

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: [email protected] [mailto:[email protected]] On
Behalf Of T. McQuinn
Sent: 18 September 2012 19:50
To: [email protected]
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

 

[T. McQuinn]

Thanks, Remo! That works perfectly.