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

Mohan K

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: March 27, 2012 3:50 PM Last updated: May 22, 2012 11:47 AM

Answer Wiki

Last updated: March 27, 2012 6:05 PM GMT

BigKat9,110 pts.
History
Contributors
- Ordered by most recent
- BigKat9,110 pts.

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

Mohan K Mar 27, 2012 6:09 PM GMT

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 Mar 27, 2012 7:09 PM GMT

What version of OS/400 are you running?

54,090 pointsBadges:

report
TomLiotta Mar 27, 2012 8:01 PM GMT

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 Mar 27, 2012 8:32 PM GMT

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 Mar 27, 2012 8:49 PM GMT

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,110 pointsBadges:

report
philpl1jb Mar 27, 2012 11:06 PM GMT

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 Mar 28, 2012 0:55 AM GMT

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 Mar 28, 2012 11:49 AM GMT

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,110 pointsBadges:

report
Mohan K Mar 28, 2012 8:05 PM GMT

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 Mar 29, 2012 3:40 AM GMT
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 Mar 29, 2012 11:15 AM GMT

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,110 pointsBadges:

report
philpl1jb Mar 30, 2012 9:13 AM GMT

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 Mar 30, 2012 11:27 AM GMT

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,110 pointsBadges:

report
philpl1jb Mar 30, 2012 11:35 AM GMT

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 Apr 16, 2012 3:04 AM GMT

[...] 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 May 21, 2012 6:10 PM GMT

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 May 22, 2012 11:47 AM GMT

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,110 pointsBadges:

report