Hi, I have this query / subquery that is acting strange. I am trying
to get the latest status of a customers Trial subscription and Paid
subscription. The customer may have 1, both or neither.

But, in the case where the customer has 1, say a Trial and not a Paid,
the entire query fails and returns nothing. Any thoughts?



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 12, 9:52*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
A cartesian product of result sets where at least one result set is
empty is still empty. You need a different approach if any of these
queries can return 0 rows. The most simple transformation (without
thinking about the efficiency of your current approach) would be to
turn the two in-line views into scalar subqueries of a main query on
DUAL like

select (query for trail_status) as trail_status, (query for
paid_status) as paid_status from dual;

Since you queries are guaranteed to return at most a single row this
should work.

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

On Apr 13, 4:16*am, Randolf Geist <mah... (AT) web (DOT) de> wrote:

Randolf,

I also tried this with no luck:


WITH test AS (
SELECT TRIAL.status trial_status, PAID.status paid_status
FROM (
SELECT co.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') TRIAL,
(SELECT co.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') PAID)
SELECT trial_status, paid_status FROM test;

On Apr 12, 9:52*pm, The Magnet <a... (AT) unsu (DOT) com> wrote:
Some create table DDL and a few inserts might allow someone to set up
the example and produce the desired result set.

HTH -- Mark D Powell --

On 13 Apr, 03:52, The Magnet <a... (AT) unsu (DOT) com> wrote:
There are some details to point out

1) because it uses a Cartesian product?
2) How many values(distinct) contains the column subscr_type?

Please provide an example with data.
what are the expected results?

However, in order to give an answer (although I'm not sure
I understood the problem) try using the following query:

SELECT max(trial_status), max(paid_status)
FROM (SELECT trial_status, null as paid_status
FROM (SELECT co.status trial_status
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'
order by order_date desc)
WHERE rownum = 1
union all
SELECT null, paid_status
FROM (SELECT co.status paid_status
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'
order by order_date desc )
WHERE rnum = 1) ;

HTH

Donatello Settembrino