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);
) AS T1
WHERE rnum = 1
) AS T2, (
)
WHERE rnum = 1
) AS T3;
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