• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

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

BOster

Member
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.
 
Top