How to acheive FULL OUTER JOIN on three files in IN SQLRPGLE program.

695 pts.
Tags:
AS/400 DB2
SQL/400
SQLRPGLE
Hi, I have 3 seperate files called Duty, Freight and Brokerage and i need to process all the records from all the three files into one output file based on key values matching across all the files, if i dont find the match in anyother two files then i need to populate coresponding fields with Zeroes. I have suitation like Brokerage file can contain some extra records which are  not available in Durty same way Freight file can contain some extra records which might not be available in Duty or Brokerage. 

When i process the Duty file i will be matching the Freight and Brokerage files based on 5 key fields and if i find a matched record in Frieght or Brokerage file then i will populate these values into output file else if i dont have a matched record in any one of the file then i need to populate corresponding field with Zeros. Same way i need to process the other two files and make sure i did not miss any records from any of the three files. I came to know that we can acheive this by using FULL OUTER JOIN on three files, but i could not able to use it int SQL400 Can anyone please help me to how to acheive this requirement in single SQL query.

Your help is greatly appreciated.

Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

I have created a small example to demonstrate

select * from filea
<pre>KEYA FLDA
1 1
2 2
3 3
5 5 </pre>
select * from fileb
<pre>KEYB FLDB
1 1
2 2
4 4
6 6 </pre>
select * from filec
<pre>KEYC FLDC
1 1
3 3
4 4
7 7 </pre>
select * from filea
full outer join fileb on keya = keyb
full outer join filec on keya = keyc or keyb = keyc
<pre>KEYA FLDA KEYB FLDB KEYC FLDC
1 1 1 1 1 1
2 2 2 2 – –
3 3 – – 3 3
5 5 – – – –
– – 4 4 4 4
– – 6 6 – –
– – – – 7 7 </pre>
select coalesce(keya,keyb,keyc) key, coalesce(flda,’0′) flda,
coalesce(fldb,’0′) fldb, coalesce(fldc,’0′) fldc
from filea
full outer join fileb on keya = keyb
full outer join filec on keya = keyc or keyb = keyc
<pre>KEY FLDA FLDB FLDC
1 1 1 1
2 2 2 0
3 3 0 3
5 5 0 0
4 0 4 4
6 0 6 0
7 0 0 7 </pre>

<i>Note that in my sample tables all fields were defined as character even though I have entered numeric text, hence the ‘0’ in the coalesce statements.</i>

Discuss This Question: 17  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Mohan K
    Hi BigKat, Thanks for your detail explanation of Full Outer Join. I have written my Query as same as above already But the issue is Iseries (AS400) SQL do not support FULL OUTER JOIN. So i am looking for some alternative Query with the Combination of Left outer join and Right outer Join on 3 files to acheive the same result as FULL OUTER JOIN as you mentioned above. I know alternative way(using Combination of left and right outer join) of acheiving full outer join only on two files but i am struggling to acheive the same for 3 files. Thanks in advance.
    695 pointsBadges:
    report
  • philpl1jb
    What version of OS/400 are you running?
    54,090 pointsBadges:
    report
  • TomLiotta
    But the issue is Iseries (AS400) SQL do not support FULL OUTER JOIN. The iSeries does support FULL OUTER JOIN if you are current in your versions. But you haven't told us what version/release you're running under. If you're running at V5R4 or earlier, you might need a much more complicated statement. Although V5R4 is still "supported" by IBM, it won't be for much longer. Tom
    125,585 pointsBadges:
    report
  • Mohan K
    Hi Tom and Phil, I am using V6R1 version of OS/400. When i am using FULL OUTER JOIN on SQL400 to select the the records i am getting following error, please assist. Message . . . . : Function not supported for query. Cause . . . . . : The reason code is 11: -- Code 11 -- Full outer join is not supported for this query.
    695 pointsBadges:
    report
  • BigKat
    okay, that only means something about the files or the joining of the files in the query is messing it up. It doesn't mean SQLRPGLE doesn't support it in general. can you show us your query that is generating this error?
    9,460 pointsBadges:
    report
  • philpl1jb
    Start building a simple query in interactive SQL and continue to add one item at a time until you find the issue(s).
    54,090 pointsBadges:
    report
  • TomLiotta
    Full outer join is not supported for this query. Without seeing the query, we can't guess what needs to be changed in it. As the message implies, 'full outer join' is supported; but your query isn't formatted appropriately for it or you're using FULL OUTER JOIN to do something that won't work. Tom
    125,585 pointsBadges:
    report
  • BigKat
    you may have to do the 3 way full outer join first (by using it as a subquery) and then the rest of your joins to that data set.
    9,460 pointsBadges:
    report
  • Mohan K
    Please find my query below and help me, since i have been struggling for 2 days to write this query. Select COALESCE(DYCDTP,FRCDTP,BRCDTP) Dtp, COALESCE(DYNCPR,FRNCPR,BRNCPR) Prd, COALESCE(DYNDFK,'0') Df, COALESCE(DYNDPT,'0') Dp, COALESCE(FRNFFK,'0') Ff, COALESCE(FRNFPT,'0') Fp, COALESCE(BRNBFK,'0') Bf, COALESCE(BRNBPT,'0') Bp From kodavmxp/FMTADY01 FULL OUTER JOIN kodavmxp/FMTAFR01 On DYCDTP = FRCDTP And DYNCPR = FRNCPR FULL OUTER JOIN kodavmxp/FMTABR01 On DYCDTP = BRCDTP And DYNCPR = BRNCPR
    695 pointsBadges:
    report
  • TomLiotta
    This is my third try at posting this. It's the FROM clause a little cleaner and with correlatives added to help in the discussion:
    From  kodavmxp/FMTADY01 t1
FULL OUTER JOIN  kodavmxp/FMTAFR01 t2
  On t1.DYCDTP = t2.FRCDTP And t1.DYNCPR = t2.FRNCPR
FULL OUTER JOIN  kodavmxp/FMTABR01 t3
  On t1.DYCDTP = t3.BRCDTP And t1.DYNCPR = t3.BRNCPR
    There are two FULL OUTER JOINs. The first tells how t1 and t2 match and also what values to use for t1 when a row exists in t2 but not in t1 and vice versa. But it doesn't help determine what value to use from t3 when there is no row in t1. It doesn't say how t2 and t3 match nor what to do when they don't match. The second tells how t1 and t3 match and also what values to use for t1 when a row exists in t3 but not in t1 and vice versa. But it doesn't help determine what value to use from t2 when there is no row in t1. It also doesn't say how t2 and t3 match nor what to do when they don't match. The FROM clause results in ambiguous results. You need to define all possible combinations. What exactly do you want to happen for each of them? Tom
    125,585 pointsBadges:
    report
  • BigKat
    Your statement is missing the equivalent of this part of my example statement: select * from filea full outer join fileb on keya = keyb full outer join filec on keya = keyc or keyb = keyc
    9,460 pointsBadges:
    report
  • philpl1jb
    Big Cat, that's a wield proposal. The addidional code would not be necessary for inner joins But it makes sense with the three file full join (it makes sense for a few seconds and then the image clouds over) p H i L
    54,090 pointsBadges:
    report
  • BigKat
    Phil, the inner join works without it, because you only want the data where there is a match, so the keyb = keyc is allready selected by the keya = keyc. Mohan, in answer to your alternative as a combination of queries, you could UNION a LEFT OUTER JOIN to a RIGHT EXCEPTION JOIN
    9,460 pointsBadges:
    report
  • philpl1jb
    You sir, by the authority vested in me, shall be dubbed. FULL OUTER JOIN BIG CAT DUKE OF SQL.
    54,090 pointsBadges:
    report
  • The most-watched IT questions: April 12, 2012 - ITKE Community Blog
    [...] 9. Philpl1jb and BigKat have fun discussing how to achieve FULL OUTER JOIN on three files in a SQLRPGLE program. [...]
    0 pointsBadges:
    report
  • Mohan K
    Hi BigKat, My Full Outer join query works now, Thanks for your detailed query that helps me. Initially i was trying Full Outer Join on Logical files, that time it was saying error that "Function not supported for query", but when i tried the same query with Physical files it worked. It seems full outer join works only with physical files. Can you please suggest me any website to learn advanced SQL query writing and any website with excercises to practice for different scenarios.
    695 pointsBadges:
    report
  • BigKat
    Hi Mohan, Glad we got your query working :) As for websites, I don't know of any one site to go to. I just kind of picked it up along the way from bits and pieces that I came across and trial and error using simplified scenarios like the one in the answer and working with it until I get the right results.
    9,460 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: