Results 1 to 10 of 10

Thread: business view on same tables

  1. #1
    Member
    Join Date
    Feb 2001
    Posts
    511

    business view on same tables

    Hi all.

    I need to write a price audit report. The BA says the business view need to be a left outer join on F42199 and F42199 (ledger). (not a typo)

    Has anyone done a business view with the same tables?

    BSVW designs let me do it.

    I'm wondering if there is anything I need to watch out for and if it is possible.

    Thanks.
    C Ho

    E9.1 TR9.1.4.3 SQL Server 2008

  2. #2
    Member nkuebelbeck's Avatar
    Join Date
    Mar 2012
    Location
    Minnesota
    Posts
    532
    this makes absolutely no sense to me...
    Analyst/Developer
    E1: 9.2 | TR: 9.2.1.4 | JAS: WebLogic | ES: OS400
    DB: DB2 | WebDev Client: Win7Pro (64 bit), VS 2010

  3. #3
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,187
    Perhaps there was a typo?

    If not - what fields are the two tables supposed to be joined by? The obvious ones of DOCO and LNID make no sense.
    Larry Jones
    E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
    Wintel, BI Publisher

    Migrating to JDE 9.2 (TR 9.2.2.4) and SQL Server 2016

  4. #4
    Member
    Join Date
    Feb 2001
    Posts
    511
    The new UBE is price audit report over F42199. There is over 1 million in F42199 even with archiving. The reasoning is data selection over F42199 (1) would shrink number of records where override = 1 and status = 620, but UBE still needs to print info where override = 0 (F42199 (2)) (original price).

    The join would be on KCOO, DOCO, DCTO, LNID. Price changes for each item must be captured thus LNID.
    C Ho

    E9.1 TR9.1.4.3 SQL Server 2008

  5. #5
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,187
    Still don't understand why link F42199 to itself. Did you perhaps mean F4211 -> F42199 ?
    Larry Jones
    E1 9.1 - TR 9.1.5 on Win 2012 R2. Oracle DB 11.2.04
    Wintel, BI Publisher

    Migrating to JDE 9.2 (TR 9.2.2.4) and SQL Server 2016

  6. #6
    Member
    Join Date
    Feb 2001
    Posts
    511
    To improve performance. BA saying that F4211-> F42199 might miss some records (forgot explanation).

    We are abandoning this self join business view idea and going back to the drawing board.
    C Ho

    E9.1 TR9.1.4.3 SQL Server 2008

  7. #7
    Member craig_welton's Avatar
    Join Date
    Oct 2000
    Location
    Litchfield, CT
    Posts
    959
    Just to add to this ... While a self-join in SQL is perfectly acceptable and useful (think of F0101 to F0101 by the related AB #s), the web client does not support it (last time I tried anyway). You can create the BSVW fine, but if it's used in a report where data selection can be applied (via the web), it throws an error. The old SQL view solution can be applied. I will try this is 9.2 when I get a minute to see if it's fixed, but I doubt it.

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

    E1 9.0 8.98.4.2 Wintel SQL 2008
    E1 9.2 9.2.1.4 iSeries

  8. #8
    Senior Member peterbruce's Avatar
    Join Date
    Jan 2004
    Location
    NSW Australia
    Posts
    2,122
    CHo,

    This is a week late but ... You could use a database view with a virtual JDE table.
    Thanks, Peter

    "Give a person a fish, feed them for a day, teach them how to fish, feed them for a lifetime."

    E9.1 TR9.1.2.1, Enterprise Server: Sun, Database Server: Sun, Oracle DB: 11g, Weblogic.
    Create!form 7

  9. #9
    Member
    Join Date
    Feb 2001
    Posts
    511
    Hi.

    Thanks for all the responses.

    The BA has gone back to the drawing board. I've been told that they do not want to do a database view over virtual JDE table.
    C Ho

    E9.1 TR9.1.4.3 SQL Server 2008

  10. #10
    New Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    30
    In situations like this, you might be able to use a subsection join in your UBE. Use the parent section to filter out the item(s) of interest, and the child to extract additional details. An example would be if you need to get the chart of accounts from F0901 for business units that have a certain object account. The parent section would have a set selection filtering the desired object account, and the subsection would be joined on parent.GMMCU = child.GMMCU. Your requirement might be met if you make your join on a subset of the primary key (SLKCOO, SLDOCO, SLDCTO, and SLLNID).
    Regards,
    Kim Schmidt

    - E1 9.1 ,TR 9.1.7 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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 and, or Oracle.