Re: Failing Query

From: The Magnet <art_at_unsu.com>
Date: Wed, 13 Apr 2011 10:43:00 -0700 (PDT)
Message-ID: <a8fce9d2-5772-48dd-ac92-3be11ae4ec44_at_v16g2000vbq.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/

Think I have it. Pain in the butt to do it, but maybe this is it!

SELECT trial_status, paid_status
FROM (SELECT trial_status, paid_status

           FROM (SELECT co.status trial_status, null 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)
          UNION
          (SELECT trial_status, paid_status
           FROM (SELECT null trial_status, 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:43:00 CDT