Re: Failing Query

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Wed, 13 Apr 2011 19:47:01 +0200
Message-ID: <io4nin$i4k$1_at_dont-email.me>

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 Received on Wed Apr 13 2011 - 12:47:01 CDT