Re: Trouble with an outer join

["Carel Teijgeler" <coteijgeler@xxxxxxxxx>]

Have you tried to reverse the file order in the second query?

You will no only receive one recors as the first PF has 1 record. Having
said that, I think it strange it will only return one record, as the second
PF has three records with the same sequence number and i would think all
three records should be joined. (But then I did not test this.)

With regards,
Carel Teijgeler


*********** REPLY SEPARATOR  ***********

On 6-8-2008 at 12:01 Emily Smith wrote:

> 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
> Programmer/Analyst
> Bank Data Services
> (618)659-4550
>
>
> CONFIDENTIALITY NOTICE: This email message is private,
> confidential property of the sender, and the materials
> may be privileged communications intended solely for
> the receipt, use, benefit, and information of the intended
> recipient indicated above. If you are not the intended
> recipient, you are hereby notified that any review,
> disclosure,distribution, copying or taking of any
> other action in reference to the contents of this message
> is strictly prohibited, and may result in legal liability
> on your part. If you have received this message in error,
> please notify the sender immediately and delete this
> message from your system. We believe that this email
> and any attachments are free of any virus or other defect
> that might affect any computer system that it is received
> and opened in, however, it is the responsibility of the
> recipient to ensure that it is virus free and the sender
> accepts no responsibility for any loss or damage.
> -- 
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> Internal Virus Database is out of date.
> Checked by AVG. 
> Version: 8.0.101 / Virus Database: 270.5.10/1584 - Release Date: 31-7-2008
> 12:00