Re: Failing Query

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Wed, 13 Apr 2011 19:18:17 +0200
Message-ID: <90m46vFqtpU1_at_mid.individual.net>

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 end
http://blog.rubybestpractices.com/

Received on Wed Apr 13 2011 - 12:18:17 CDT