Bad Behaving Data Selection

DBohner-(db)

Legendary Poster
Using BSVW V8300001 - I do Data Selection a:
F0902.Ledger Type = AA
F0902.OBJ 3000-4500
F0901.Company = 65
F0902.Year = 9

What I get is:
WHERE ( ( ( T1.GBLT = 'AA' OR T1.GBLT IS NULL ) AND ( T1.GBOBJ BETWEEN '3000' AND '4500' OR T1.GBOBJ IS NULL )
AND T0.GMCO = '00065' AND ( T1.GBFY = 9.000000 OR T1.GBFY IS NULL ) ) ) ORDER BY T1.GBMCU ASC,T1.GBSBL ASC

Note all the nulls??? WTH? From a performance aspect, it goes from selecting the 11,018 records it should have selected to 578,188 records it did select (with the F0902 NULLs)

Is this the way Outer Joins should work? Why does the crazy "OR NULL" have to be appended to each row of Data Selection?

Performance SUCKS!

(db)
 
JDE does this on all BVs that have a left/right outer join. Which is why you cant use a left join to effectively do a NOT IN clause.
 
Well, I have asked myself this question many times in the past in similar situations. :)
Unofficial consensus (that means the guys that were around when I ranted over this) is that since the BSVW has to "show" all tables/fields, and in an outer join the table can effectively be "null", for every given value in a data selection, JDE has to consider the possibility of that field returning "null", but still has to show that in a report section or grid. So... null.

Depending on how mad you are at this, you can go as far as "spliting" this BSVW in two, just to see how faster it is.

I know this doesn't help... it's just an "I feel your pain" comment. :)

Best regards!
 
I know that it does this "NULL" thang on the "Outer" selection of any outer-join, it is just FRUSTRATING.

Best option, might be to just copy the join and make it a simple join instead of an outer join...

Frankly, if I selected targeted data from the outer-side - I would NOT want any "Null" data to show up on the report. That's 500K F0901 records in the selection that should not have shown up at all...

YES, I do want the world to revolve around (db)!
 
Hi,

Did you try using a SQL 92 Left Outer join? I think that removes the OR NULL addition on any outer side criteria.

Craig
 
Being that I was (was) using a native BSVW (V8300001) - I didn't want to play monster with the existing millions of reports.

I've copied to V5583001 - and will try the '92 after I get everything re-attached (thanks for the suggestion).

(db)
 
craig, that's also the 1st thing that came to my mind when i read the original question. i looked up the definition just to make sure, and -if i understood it correctly- it seems to confirm what you say:

"A Left Outer Join is the same as the standard SQL 92 Left Outer Join except that records without a matching right side are always included, ignoring any query against right-side columns. In other words, records with null as values for the right-side columns are always included in the section regardless of any WHERE clause against the right-side columns."

ps: one thing i noticed is that the sql 92 left outer is not available in older versions of jde.

- - -
edit:

addition to my "not available in older versions" remark: available as of EOne 8.9 according to this oracle knowledge base document (btw: it also mentions a workaround without having to modify or replace the original businessview: "add an additional data selection based on the left outer join keys such that they must match each other i.e. TO Join Key_01 = T1.Join Key_01"):

E1: TDA: Null addition with a left outer join business view [ID 632226.1]
 
[ QUOTE ]
Did you try using a SQL 92 Left Outer join? I think that removes the OR NULL addition on any outer side criteria.


[/ QUOTE ]

Huh. We just upgraded to 9.0 from Xe, didn't know that this was now an option. Learn something new every day.
 
If you do not want nulls, use V8300005. It is built over the same three tables (F0006, F0901, F0902), but it uses an inner join.

V8300001 is used with the Financial Reports director template. It uses an outer join so it will return all accounts even if there are no rows in F0902. It was deliberately created for financial reports which do not have row specifications. In addition to accounts with balances, the outer join also returns the non-posting title accounts (PEC=N) which are required for headers and account level of detail rollups.

V8300005 is used with the Financial Row Reports director template. It uses an inner join so it will only return accounts with matching rows in F0902 and runs more efficiently. It was deliberately created for financial reports which have row specifications. For reports with row specifications, a constant row is specified for headers and a sum or calculation row is specified for subtotals/level of detail rollups. Thus the non-posting accounts are not needed to define the structure of the report.

Brad

World F6.2 - A7.3
OneWorld B7321 - EnterpriseOne 8.12
 
Back
Top