RE: Trouble with an outer join

Emily,

Based on your description, I think you're going for a FULL OUTER JOIN.
This is available on DB2 for i 6.1, but not on previous releases. On 6.1
you'd do something like:

SELECT A."SEQUENCE", A.DESCRIPT, A.SEL1,
B."SEQUENCE", B.DESCRIPT, B.SEL1
FROM brndetlf A full outer join brndetlf2 B
ON A."SEQUENCE" = B."SEQUENCE"

You can simulate full outer join on prior releases though, and you have
gotten very close to the solution with your original query.
Try this instead:

SELECT BRNDETLF."SEQUENCE", BRNDETLF.DESCRIPT,
BRNDETLF.SEL1, BRNDETLF2."SEQUENCE",
BRNDETLF2.DESCRIPT, BRNDETLF2.SEL1
FROM BRNDETLF LEFT OUTER JOIN BRNDETLF2
ON BRNDETLF."SEQUENCE" = BRNDETLF2."SEQUENCE"
UNION ALL
SELECT BRNDETLF."SEQUENCE", BRNDETLF.DESCRIPT,
BRNDETLF.SEL1, BRNDETLF2."SEQUENCE",
BRNDETLF2.DESCRIPT, BRNDETLF2.SEL1
FROM BRNDETLF2 EXCEPTION JOIN BRNDETLF
ON BRNDETLF2."SEQUENCE" = BRNDETLF."SEQUENCE"

Hope that helps, and by all means, let me know if I totally missed the mark
on my interpretation of your intentions.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Trouble with an outer join

I'm attempting to create an outer join in query management. Here is the
statement

SELECT BRNDETLF."SEQUENCE", BRNDETLF.DESCRIPT,
BRNDETLF.SEL1, BRNDETLF2."SEQUENCE",
BRNDETLF2.DESCRIPT, BRNDETLF2.SEL1
FROM BRNDETLF LEFT OUTER JOIN BRNDETLF2
ON BRNDETLF."SEQUENCE" = BRNDETLF2."SEQUENCE"
UNION
SELECT BRNDETLF."SEQUENCE", BRNDETLF.DESCRIPT,
BRNDETLF.SEL1, BRNDETLF2."SEQUENCE",
BRNDETLF2.DESCRIPT, BRNDETLF2.SEL1
FROM BRNDETLF EXCEPTION JOIN BRNDETLF2
ON BRNDETLF."SEQUENCE" = BRNDETLF2."SEQUENCE"

I run strqmqry and output the results to an outfile. File BRNDETLF has one
record with the following data:
Sequence = 01 , Descript = 'Bundled Twenties' , Sel1 = 600.00
File BRNDETLF2 has three records with the following data:
Record 1 Sequence = 01, Descript = 'Loose Twenties', Sel1 = 100.00
Record 2 Sequence = 01, Descript = 'Loose Twenties' , Sel1 = 60.00
Record 3 Sequence = 01, Descript = 'Loose Twenties' , Sel1 = 180.00

When I run the query it only creates one record in the outfile :
Sequence = 01, Descript = 'Bundled Twenties', Sel1 = 600.00, Sequence2 = 01,
Descript2 = 'Bundled Twenties'
Sel2 = 100.00

What I'm trying to accomplish is that it would output three records. The
first like the record it's already creating and then
two others with null values in Sequence, Descript, and Sel1 and with the
values from BRNDETLF2 in Sequence2, Descript2 and
Sel2. Also, if there are cases where records exist in BRNDETLF but not in
BRNDETLF2 I want records output with values from brndetlf going to the first
three fields and null values going to the last three in my outfile.

Can someone help get me squared away on this?

Emily Smith