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

On 13.04.2011 17:39, The Magnet wrote:
I am not sure I find this example simple.

In what ways does it fail?

Which is "top" and which is "bottom" in your lingo? I cannot find a
WHERE clause at the main SELECT.

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/

On Apr 13, 12:18*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
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);

On Apr 13, 12:18*pm, Robert Klemme <shortcut... (AT) googlemail (DOT) com> wrote:
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);

On 04/13/2011 05:39 PM, The Magnet wrote:
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

On Apr 13, 12:47*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
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?

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.

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

On Apr 13, 2:39*pm, Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com>
wrote:
Lennart,

You the man. Thank you so very much!!!

That is a good and somewhat complex query. Very nice. Thank you!

Lennart Jonsson <erik.lennart.jons... (AT) gmail (DOT) com> wrote:
max() keeps are generally more efficient than row_number() = 1

select max(decode(subscr_type, 'Trial', co.status, null)) keep
(dense_rank last order by
decode(subscr_type, 'Trial', 1, 0),
co.order_date)
as trial_status,
max(decode(subscr_type, 'Trial', null, co.status)) keep
(dense_rank last order by
decode(subscr_type, 'Trial', 0, 1),
co.order_date)
as paid_status
from customer_order_vw co
join newsletter_subscription ns on ns.code = co.code
where co.customer_id = 732126295
and subproduct_id = 197

--
Peter

On Apr 13, 6:13*pm, Peter Nilsson <ai... (AT) acay (DOT) com.au> wrote:
Peter,

This is also a good solution. Many thanks!