Login
Enter your username and password to login. If you do not have a username you can register one.

Username: 
Password: 
Remember Me



Forgotten Password?

Online
25 guests have been online with in the last 10 minutes.

Hot Topic
No posts have caught fire as of yet.

JD Edwards® Enterprise One / One World Discussions >> JD Edwards® EnterpriseOne Developers

Pages: 1
DBohner-(db)
member


Reged: 01/17/01
Last on: 04/24/14
Posts: 2565
Loc: Meridian, ID
Bad Behaving Data Selection
      #156217 - 03/02/10 01:01 PM

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)

--------------------
Daniel Bohner
Independent E1 Developer/Techno-Functional Consultant
Member - iConsortium
www.JDERESEARCH.COM
drbohner@JDEResearch.com | 208.495.4JDE
www.linkedin.com/in/drbohner
Worked on Everything


Post Extras: Print Post   Remind Me!   Notify Moderator  
BOster
member


Reged: 03/23/04
Last on: 04/24/14
Posts: 979
Loc: Fort Worth, Texas
Re: Bad Behaving Data Selection
new [Re: DBohner-(db)]       #156224 - 03/02/10 02:28 PM

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.

--------------------
Brian Oster
Application Development Manager
E1 9.0 (TR8.98.4.0)
MSSQL 2008
Websphere 7.0
Servers: Win2008 (64 bit)
WebDev Client: WinXP (32 bit)


Post Extras: Print Post   Remind Me!   Notify Moderator  
MPDev
member


Reged: 01/12/05
Last on: 12/13/13
Posts: 94
Loc: Brazil
Re: Bad Behaving Data Selection
new [Re: DBohner-(db)]       #156225 - 03/02/10 02:36 PM

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!

--------------------
-----
Marcelo

Currently E812, Tools 8.98.24, Windows, Oracle10g, OAS 10.1.3, JDev 10.1.3.1




Post Extras: Print Post   Remind Me!   Notify Moderator  
DBohner-(db)
member


Reged: 01/17/01
Last on: 04/24/14
Posts: 2565
Loc: Meridian, ID
Re: Bad Behaving Data Selection
new [Re: MPDev]       #156226 - 03/02/10 02:43 PM

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

--------------------
Daniel Bohner
Independent E1 Developer/Techno-Functional Consultant
Member - iConsortium
www.JDERESEARCH.COM
drbohner@JDEResearch.com | 208.495.4JDE
www.linkedin.com/in/drbohner
Worked on Everything


Post Extras: Print Post   Remind Me!   Notify Moderator  
craig_welton
member


Reged: 10/31/00
Last on: 04/24/14
Posts: 524
Loc: Litchfield, CT
Re: Bad Behaving Data Selection
new [Re: DBohner-(db)]       #156227 - 03/02/10 02:53 PM

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

--------------------
Craig Welton
PatWel Group Inc.
http://www.patwel.com
Home of the FREE JDE Object Browser and JDETrace Tools

E1 9.0 8.98.3 iSeries
E1 9.0 8.98.4.2 Wintel SQL 2008


Post Extras: Print Post   Remind Me!   Notify Moderator  
DBohner-(db)
member


Reged: 01/17/01
Last on: 04/24/14
Posts: 2565
Loc: Meridian, ID
Re: Bad Behaving Data Selection
new [Re: craig_welton]       #156228 - 03/02/10 03:18 PM

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)

--------------------
Daniel Bohner
Independent E1 Developer/Techno-Functional Consultant
Member - iConsortium
www.JDERESEARCH.COM
drbohner@JDEResearch.com | 208.495.4JDE
www.linkedin.com/in/drbohner
Worked on Everything


Post Extras: Print Post   Remind Me!   Notify Moderator  
Remo Williams
member


Reged: 08/10/04
Last on: 04/24/14
Posts: 528
Loc: NYC, Lower East Side
Re: Bad Behaving Data Selection
new [Re: craig_welton]       #156250 - 03/03/10 04:20 AM

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]

Edited by remo (03/03/10 04:43 AM)


Post Extras: Print Post   Remind Me!   Notify Moderator  
BOster
member


Reged: 03/23/04
Last on: 04/24/14
Posts: 979
Loc: Fort Worth, Texas
Re: Bad Behaving Data Selection
new [Re: craig_welton]       #156264 - 03/03/10 09:34 AM

Quote:

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





Huh. We just upgraded to 9.0 from Xe, didn't know that this was now an option. Learn something new every day.

--------------------
Brian Oster
Application Development Manager
E1 9.0 (TR8.98.4.0)
MSSQL 2008
Websphere 7.0
Servers: Win2008 (64 bit)
WebDev Client: WinXP (32 bit)


Post Extras: Print Post   Remind Me!   Notify Moderator  
JdeInNJ
member


Reged: 06/03/09
Last on: 04/01/14
Posts: 280
Loc: New Jersey, USA
Re: Bad Behaving Data Selection
new [Re: DBohner-(db)]       #156364 - 03/05/10 09:14 AM

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

--------------------
Brad
OneWorld B7321 - EnterpriseOne 9.1
World F6.2 - A7.3


Post Extras: Print Post   Remind Me!   Notify Moderator  
Pages: 1


Extra information
2 registered and 21 anonymous users are browsing this forum.

Moderator:  Sef, ekempter, Christian Audet, jdecnc 

Print Thread

Forum Permissions
      You cannot start new topics
      You cannot reply to topics
      HTML is disabled
      UBBCode is enabled

Rating:
Thread views: 4075

Rate this thread

Jump to

JDELIST Support | Privacy statement JDELIST.com

*
UBB.threads™ 6.4.1

 .: IIP 5.4.5 by David Lozier ©2003 :. 
Distributed by ThreadsDev.com

The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither
endorsed nor approved by JD Edwards® & Company, Oracle or Peoplesoft.

This page best viewed with Netscape 4 or Microsoft Explorer 4 or above in 800 x 600 resolution.