Results 1 to 2 of 2

Thread: implement left outer join in event rule...

  1. #1

    implement left outer join in event rule...

    HI . I am very new in JDE. I used to be Oracle developer. JDE report design Aid really throw me off and I hope someone can help me.

    I have a very simple SQL
    select Ne.nenedl01, ab.aban8, ww.wwGnme, ea.eaemal
    from F1501B ne
    inner join F0101 ab on ab.aban8 = ne.nean8
    left outer join F0111 ww on ab.aban8 = ww.wwan8
    left outer join F01151 ea on ww.wwan8 = ea.eaan8;

    So I translate it into JDE report
    a Business view on F1501B + F0101
    in the event rule :
    F0111.select ( Ab.aban8 = ww.wwan8 )
    F0111.fetch next
    while file_io_status is equal to CO Success
    F01151.select ( wwan8 = ea.eaan8 )
    F01151.fetch next
    while file_io_status is equal to co success
    print "Ne.nenedl01, ab.aban8, ww.wwGnme, ea.eaemal";
    end while
    end while

    But in JDE, if the person do NOT have any email, that row of data will not print. I search around, I really cannot an idea on how JDE handle this kind....

  2. #2
    Senior Member
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas
    Posts
    1,582
    Couple of general tips.

    1. You can do table I/O against a business view (BSVW) not just individual tables so you can effectively create the SQL you listed above using a BSVW instead of trying to replicate the results with ER code against the individual tables. It will probably also be a lot faster.
    2. In a Business View there are two types of Left Joins. One called something like "Left Outer..." and one called "SQL-92...". The first will list the individual tables in the FROM clause and construct the join in the where clause instead of using the JOIN key word and JDE will add in the "is null clause" the latter will use the join keyword.

    "Left Join..."
    Code:
    ...
    from f0101 t1, f0116 t2
    where t1.an8 = t2.an8 or t2.an8 is null
    Or something like that... can't remember off the top of my head

    "SQL-92 Left Join"
    Code:
    ...
    from f0101 t1
    left join f0116 t2
    on t1.an8=t2.an8
    3. Turn on your debug log so you can view the actual SQL being generated. For anything remotely complex I will do this, grab the SQL and run it in something like SQL Server Management Studio or other SQL client to make sure it is actually creating the SQL and result set I intended.
    4. The JDEBASE C APIs have a lot more functionality than ER code for creating all kinds of SQL including better WHERE clause construction (parenthesis, In/Not In clause, etc.), aggregates (sum, avg, count, etc. with group by clause), order by clauses, having clauses, etc.
    5. For really complex SQL that you just can't construct with a business view you can create what a lot of us call a "Virtual Table". This is just a DB server SQL view with a JDE table definition that matches the resulting record set (this JDE virtual table is of course read only). This allows for things like sub queries, etc. - really just about anything you can do in core SQL that produces a recordset.
    Brian Oster
    Application Development Manager
    E1: 9.0 (TR9.1.5.1) / 9.2 (TR9.2.2.2)
    JAS/BSSV: Weblogic 12.1.2 / Weblogic 12.2
    ES: Win2008 / Win2016
    DB: MSSQL 2014 / 2016
    WebDev Client: Win7Pro / Win10Pro

Thread Information

Users Browsing this Thread

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

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.