Or you might use
select pcode as Code, NVL(pemp.name, pempex.name) as 'name';
, NVL(pemp.gross, pempex.gross) as 'gross' etc... ;
from palldets ;
left join pemp ;
on palldets.pcode=pemp.code ;
left join pempex ;
on palldets.pcode=pempex.code ;
where cycle=mycycle ;
order by pcode,date,cycle ;
into table (myfile2)
If there might be pemp records not found neither in pemp nor in pempex and you
don't want them in your result set then you use a """having pemp.code is not
.NULL. or pempex.code is not .NULL.""" or you further filter the result set.
El 04/05/12 07:14, Dave Crozier escribió:
> Sytze,
> Don't forget to make the Union a "union all" statement if you wish to get all
> records from the two selects, even though there may be duplicates.
>
> By itself "union" ignores duplicate records.
>
> Dave
>
> -----Original Message-----
> From: profox-boun...@leafe.com [mailto:profox-boun...@leafe.com] On Behalf Of
> Paul Newton
> Sent: 04 May 2012 09:17
> To: profox@leafe.com
> Subject: RE: Help on joins please
>
> Sytze
>
> You need two select statements with a union like this
>
> Select ......... Union (Select .............)
>
> The first select will refer to pemp and the second select refers to pempex
>
> Paul
>
> -----Original Message-----
> From: profoxtech-boun...@leafe.com [mailto:profoxtech-boun...@leafe.com] On
> Behalf Of Sytze de Boer
> Sent: 04 May 2012 06:15
> To: profoxt...@leafe.com
> Subject: Help on joins please
>
> In a routine of mine, I do the following and all is fine select pcode as
> Code, pemp.name, gross,tax,nontgross as Non_Taxables ,deductions from
> palldets ; join pemp on palldets.pcode=pemp.code ; where cycle=mycycle order
> by pcode,date,cycle into table (myfile2)
>
> But sometimes, the employee is in a file called pempex select pcode as Code,
> pemp.name, gross,tax,nontgross as Non_Taxables ,deductions from palldets ;
> join pemp on palldets.pcode=pemp.code ; join pempex on
> palldets.pcode=pempex.code; where cycle=mycycle order by pcode,date,cycle
> into table (myfile2)
>
> The 2nd join does not work
> 1 How do I make this work IF data needs to be found in pempex
> 2 How to make this work if in fact all the data is ONLY in pemp
>
>
> --
> Regards
> Sytze de Boer
>
>
> --- StripMime Report -- processed MIME parts --- multipart/alternative
> text/plain (text body -- kept)
> text/html
> ---
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/4fa3cde4.6060...@gmail.com
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
