Re: Failing Query

From: The Magnet <art_at_unsu.com>
Date: Wed, 13 Apr 2011 10:25:45 -0700 (PDT)
Message-ID: <b5466d09-e529-4e7d-a3fe-a57a12bd5a9b_at_v31g2000vbs.googlegroups.com>

On Apr 13, 12:18 pm, Robert Klemme <shortcut..._at_googlemail.com> wrote:
> On 13.04.2011 17:39, The Magnet wrote:
>
> > I posted something similar to this elsewhere, but maybe a simpler
> > example will help.
>
> I am not sure I find this example simple.
>
> > Why is this failing?
>
> In what ways does it fail?
>
> >  In this query, the top query does have a
> > matching criteria, the bottom does not.
>
> Which is "top" and which is "bottom" in your lingo?  I cannot find a
> WHERE clause at the main SELECT.
>
>
>
>
>
>
>
>
>
> >  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);
>
> A few things strike me as odd:
>
> - You select much more in those inline views than you extract with the
> main query.
>
> - There is no join condition between both inline views so you get a full
> join
>
> - Is it intentional that two different customer_id are used?
>
> Why not something like
>
> SELECT t.status trial_status, p.status paid_status
> FROM customer_order_vw t
> JOIN newsletter_subscription nst
>    ON t.code = nst.code
>   AND nst.subscr_type = 'Trial'
> , customer_order_vw p
> JOIN newsletter_subscription pst
>    ON t.code = pst.code
>   AND pst.subscr_type <> 'Trial'
> WHERE t.customer_id = p.customer_id
>    AND t.subproduct_id = p.subproduct_id
>    AND t.customer_id = 931044855
>    AND t.subproduct_id = 197
>
> Note, I don't think it is a good idea to join here as it will create
> duplicate information.  I would also consider
>
> SELECT t.status
> , CASE nst.subscr_type
>    WHEN 'Trial' then 'trial_status'
>    ELSE 'paid_status'
>    END status_type
> FROM customer_order_vw t
> JOIN newsletter_subscription nst
>    ON t.code = nst.code
> WHERE t.customer_id = 931044855
>    AND t.subproduct_id = 197
>
> Kind regards
>
>         robert
>
> --
> remember.guy do |as, often| as.you_can - without endhttp://blog.rubybestpractices.com/

Robert,

My bad,I'm just so frustrated. Yes, the CUSTOMER_ID should be the same, and forget about the extra columns, I was trying some things.

But basically I am looking for the most recent Trial, and the most recent Paid for the customer. It is possible they may only have one. In that case, the query is not returning any rows. It should, I would think, return 1 row, with NULL in the column where nothing is found.

So, it should be like this:

SELECT trial_status, paid_status
FROM (SELECT trial_status

           FROM (SELECT co.status trial_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),
          (SELECT paid_status
           FROM (SELECT 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);

Received on Wed Apr 13 2011 - 12:25:45 CDT