Results 1 to 9 of 9

Thread: Bad Behaving Data Selection

  1. #1
    Senior Member
    Join Date
    Jan 2001
    Location
    Meridian, ID (and wherever there is work)
    Posts
    2,576

    Bad Behaving Data Selection

    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

  2. #2
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,076

    Re: Bad Behaving Data Selection

    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)

  3. #3
    Member MPDev's Avatar
    Join Date
    Jan 2005
    Location
    Brazil
    Posts
    94

    Re: Bad Behaving Data Selection

    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



  4. #4
    Senior Member
    Join Date
    Jan 2001
    Location
    Meridian, ID (and wherever there is work)
    Posts
    2,576

    Re: Bad Behaving Data Selection

    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

  5. #5
    Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    580

    Re: Bad Behaving Data Selection

    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

  6. #6
    Senior Member
    Join Date
    Jan 2001
    Location
    Meridian, ID (and wherever there is work)
    Posts
    2,576

    Re: Bad Behaving Data Selection

    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

  7. #7
    Member
    Join Date
    Aug 2004
    Location
    NYC, Lower East Side
    Posts
    526

    Re: Bad Behaving Data Selection

    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]
    Technical Consultant Xe-9.1

  8. #8
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,076

    Re: Bad Behaving Data Selection

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

  9. #9
    Member
    Join Date
    Jun 2009
    Location
    New Jersey, USA
    Posts
    280

    Re: Bad Behaving Data Selection

    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

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Data Selection Literal Part Import Data
    By b0rj in forum JD Edwards® EnterpriseOne Developers
    Replies: 14
    Last Post: 11-23-2014, 11:25 PM
  2. New extra data selection line automatically pop up and lock all data selection lines
    By BhushanC in forum JD Edwards® EnterpriseOne Developers
    Replies: 0
    Last Post: 05-25-2011, 08:26 AM
  3. Workflow behaving differently in DV & PD
    By anishshetty in forum JD Edwards® EnterpriseOne Developers
    Replies: 1
    Last Post: 07-17-2009, 01:14 PM
  4. Hide data selection and data secquence screen?
    By ned in forum JD Edwards® EnterpriseOne Developers
    Replies: 4
    Last Post: 06-12-2005, 07:07 PM
  5. R31804, behaving abnormally
    By Kamal Ghosh in forum JD Edwards® EnterpriseOne Applications
    Replies: 0
    Last Post: 02-01-2004, 10:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
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.