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
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