• JDELIST is celebrating its 20 year anniversary today!
  • 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!

Which left outer join to use?

Andrew2009

Well Known Member
Below is a join for my business view

F0901.AID Simple(==) = F0902.AID
F0901.MCU Simple(==) = F0006.MCU

Data selection is below. Only FY is set in the initialize section of the UBE. All others are set when users run the report

F0902.FY = 17
F0902.LT = JA or JU
F0006.MCU = 113246
F0006.PECC != N

I have records in F0901 that DO NOT exist in F0902 yet but I want to include these records in the result.

I tried both SQL 92 and regular left outer join as mentioned in the document (Doc ID 646081.1) but they both do not work.

Do you have any ideas?

Thanks

JDE 9.1
 

craig_welton

Legendary Poster
How about

F0901.MCU Simple to F0006.MCU
F0901.AID Left Outer to F0902.AID

The query should append OR IS NULL for each inclusion of a F0902 column in the WHERE clause.

Craig
 

BOster

Legendary Poster
Records that are NOT in the F0902 will also NOT have a FY=17 or an LT=JA,JU...

Edit 1:
...or what Craig said. I think it should work with a normal left outer because, like Craig said, it should append is null to the query. In cases like this I turn on debug log and get the actual SQL that is generated to see what is really happening. I suggest doing that and if the problem is immediately apparent, post the SQL back here.
 
Last edited:
Top