Re: Outer join

On 18 Sep, 20:10, David Portas
<REMOVE_BEFORE_REPLYING_dpor..._at_acm.org> wrote:
> On 18 Sep, 17:26, mje..._at_gmail.com wrote:
>
>
>
>
>
>
>
> > Hi Dan,
>
> > Here is another one that does not seem to be working.  I'm not sure
> > what I am doing wrong........
>
> > SELECT DISTINCT(e.customer_id),
> > DECODE(action,'A','Active','D','Inactive') "Status", action_date,
> >        MAX(action_date) KEEP (DENSE_RANK LAST ORDER BY action_date)
> > OVER (PARTITION BY e.customer_id) "STUFF"
> > FROM email_product_hist e LEFT OUTER JOIN  subscr_email_product s ON
> > e.customer_id = s.customer_id
> > WHERE action_date BETWEEN TO_DATE('06012008','MMDDYYYY') AND
> > TO_DATE('09152008','MMDDYYYY')
> >   AND e.email_product_id = 'PPM'
> > ORDER BY e.customer_id DESC, action_date DESC;
>
> > According to the documentation, the LEFT JOIN should return all rows
> > from email_product_hist and rows from subscr_email_product  that do
> > not have matching rows....
>
> Is action_date in the subscr_email_product table? You didn't include
> an alias so I can't be sure.
>
> WHERE is evaluated AFTER the outer join so that WHERE condition would
> exclude the NULLs if it refers to the unpreserved ("right") table.
>
> If I'm right then try changing it like this:
>
> ...
> FROM email_product_hist e
> LEFT OUTER JOIN  subscr_email_product s
> ON e.customer_id = s.customer_id
> AND s.action_date BETWEEN TO_DATE('06012008','MMDDYYYY')
> WHERE TO_DATE('09152008','MMDDYYYY')
>   AND e.email_product_id = 'PPM'
> ...
>

Correction:

FROM email_product_hist e
LEFT OUTER JOIN subscr_email_product s
ON e.customer_id = s.customer_id
  AND s.action_date BETWEEN

   TO_DATE('06012008','MMDDYYYY') AND
   TO_DATE('09152008','MMDDYYYY')
WHERE e.email_product_id = 'PPM'

--
David Portas