Re: Failing Query

From: Lennart Jonsson <erik.lennart.jonsson_at_gmail.com>
Date: Wed, 13 Apr 2011 21:39:52 +0200
Message-ID: <io4u69$9l7$1_at_dont-email.me>

On 04/13/2011 07:54 PM, The Magnet wrote: [...]
> 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?

No they are not, they are tables in your query. Because one of those tables are empty, the result is empty. Let's rewrite the query as:

WITH T2 AS (
    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
), T4 AS (

    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'

    ) AS T3
    WHERE rnum = 1
)
SELECT T2.trial_status, T4.paid_status
FROM T2, T4; Do you agree that if T4 is empty the result is empty?

I don't have an Oracle installation at hand to try with, but I guess something like below should do:

WITH T AS (
    SELECT co.status

	 , CASE ns.subscr_type
           WHEN 'Trial' then 'trial_status'
           ELSE 'paid_status'
           END as status_type
	 , ROW_NUMBER() OVER (
		PARTITION BY CASE ns.subscr_type
           		     WHEN 'Trial' then 'trial_status'
                             ELSE 'paid_status'
                             END		
                ORDER BY order_date DESC
          ) as rnum
        FROM customer_order_vw co
	JOIN newsletter_subscription ns
	    ON co.code = ns.code
        WHERE customer_id = 931044855
          AND subproduct_id = 197

)
SELECT status_type, status
FROM T
WHERE rnum = 1

or if you insist on pivoting:

WITH T AS (
    SELECT co.status

	 , CASE ns.subscr_type
           WHEN 'Trial' then 'trial_status'
           ELSE 'paid_status'
           END as status_type
	 , ROW_NUMBER() OVER (
		PARTITION BY CASE ns.subscr_type
           		     WHEN 'Trial' then 'trial_status'
                             ELSE 'paid_status'
                             END		
                ORDER BY order_date DESC
          ) as rnum
        FROM customer_order_vw co
	JOIN newsletter_subscription ns
	    ON co.code = ns.code
        WHERE customer_id = 931044855
          AND subproduct_id = 197

)
SELECT
    (SELECT status FROM T

     WHERE rnum = 1
       AND status_type = 'trial_status') as trial_status
   ,(SELECT status FROM T
     WHERE rnum = 1
       AND status_type = 'paid_status') as paid_status

FROM DUAL /Lennart Received on Wed Apr 13 2011 - 14:39:52 CDT