Re: Failing Query

From: The Magnet <art_at_unsu.com>
Date: Wed, 13 Apr 2011 10:54:01 -0700 (PDT)
Message-ID: <22d8616f-5db2-447d-9996-f05a90917c68_at_e9g2000vbk.googlegroups.com>

On Apr 13, 12:47 pm, Lennart Jonsson <erik.lennart.jons..._at_gmail.com> wrote:
> On 04/13/2011 05:39 PM, The Magnet wrote:
>
>
>
>
>
>
>
>
>
> > Hi,
>
> > I posted something similar to this elsewhere, but maybe a simpler
> > example will help.
>
> > Why is this failing?  In this query, the top query does have a
> > matching criteria, the bottom does not.  So, why does the entire query
> > fail?  What I thought it should do is return 2 columns, one with a
> > value and the other NULL.  Why does it return nothing?  Really, these
> > are separate subqueries, but why does everything fail is one of the
> > subqueries return no results?
>
> > SELECT trial_status, paid_status
> > FROM (SELECT trial_status
> >            FROM (SELECT order_date, co.status trial_status,
> > ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
> >                       FROM customer_order_vw co,
> > newsletter_subscription ns
> >                       WHERE customer_id = 931044855
> >                            AND subproduct_id = 197
> >                            AND co.code = ns.code
> >                            AND subscr_type = 'Trial')
> >            WHERE rnum = 1),
> >           (SELECT paid_status
> >            FROM (SELECT order_date, co.status paid_status,
> > ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
> >                       FROM customer_order_vw co,
> > newsletter_subscription ns
> >                       WHERE customer_id = 732126295
> >                            AND subproduct_id = 197
> >                            AND co.code = ns.code
> >                            AND subscr_type <> 'Trial')
> >            WHERE rnum = 1);
>
> Not sure what top query refers to. Let's start by indenting the query
> and name the derived tables:
>
> SELECT trial_status, paid_status
> FROM (
>     SELECT trial_status
>     FROM (
>         SELECT order_date, co.status trial_status,
>                ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
>         FROM customer_order_vw co, newsletter_subscription ns
>         WHERE customer_id = 931044855
>           AND subproduct_id = 197
>           AND co.code = ns.code
>           AND subscr_type = 'Trial'
>     ) AS T1
>     WHERE rnum = 1
> ) AS T2, (
>     SELECT paid_status
>     FROM (
>         SELECT order_date, co.status paid_status,
>                ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
>         FROM customer_order_vw co, newsletter_subscription ns
>         WHERE customer_id = 732126295
>           AND subproduct_id = 197
>           AND co.code = ns.code
>           AND subscr_type <> 'Trial'
>     )
>     WHERE rnum = 1
> ) AS T3;
>
> If top query refers to T2 and bottom query refers to T3, the result of
> the whole query will be an empty table because T3 is empty. Rewriting
> the query to:
>
> select trial_status, paid_status
> from T2
> cross join T3
>
> perhaps make it easier to realize this? Either use a full outer join or
> scalar subqueries instead.
>
> /Lennart

Lennart,

Maybe I am not understanding, but those subqueries are really 'columns'. So, I should be able to return a NULL for a column value, yes?

So, I have to order each column by ORDER_DATE to get the most recent on, hence the ROW_NUMBER().

I've been lost for 2 days trying hundreds of combinations. I know it can be done. Unless neither condition is true, then something should always be returned.

T1 can fail and T2 not fail, it should return a NULL for T1 and a value for T2, and the other way. Received on Wed Apr 13 2011 - 12:54:01 CDT