Re: SQL: 2=Matched records with primary file

[Vanja Jovic <vanja@xxxxxxxxx>]

Left outer join ?

Vanja Jovic,
Canada

Rob Berendt wrote:
> 
> I have a Query which works well.  I have a vendor supplied,
> non normalized file that contains the following, (among other stuff):
> IPROD, IDESC, ICLAS, IDRAW, IUMR, IVEND, IVEN2
> in which IVEND and IVEN2 are the primary and secondary
> vendors of the item.
> In the Query I select 3 files:  IIM, AVM and AVM.  I join IVEND to AVM,
> and IVEN2 also with AVM and for my join type select
> '2=Matched records with primary file'.
> 
> Worked great.  Selected records in which:
> 1)  IVEND and IVEN2 both had values
> 2)  IVEND or IVEN2 had a value
> 3)  Neither had values
> 
> Then I did a RTVQMQRY and converted this into Query Management and got:
> SELECT
>   ALL       T01.IPROD, T01.IDESC, T01.ICLAS, T01.IDRAW, T01.IUMR, T01.IVEND,
>             T01.IVEN2, T02.VNDNAM, T03.VNDNAM
>   FROM      PPLDIVF/IIM T01,
>             PPLDIVF/AVM T02,
>             PPLDIVF/AVM T03
>   WHERE     T01.IVEND = T02.VENDOR
>     AND     T01.IVEN2 = T03.VENDOR
> 
> Alas, this only selects records in which
> 1)  IVEND and IVEN2 both had values
> 
> How do I get around this?  My ultimate goal is to CREATE VIEW
> containing these fields.
> 
> I tried a 3 part UNION, but UNION is not supported by CREATE VIEW.
> However the 3 part UNION worked fine for Interactive SQL.
> +---
> | This is the Midrange System Mailing List!
> | To submit a new message, send your mail to MIDRANGE-L@midrange.com.
> | To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
> | To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator: david@midrange.com
> +---
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---