Re: Subquery
From: The Magnet <art_at_unsu.com>
Date: Wed, 13 Apr 2011 06:05:44 -0700 (PDT)
Message-ID: <78dd00b8-8bd0-4c0a-bb26-20d44c6d7cc2_at_t19g2000prd.googlegroups.com>
On Apr 13, 4:16 am, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 12, 9:52 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > 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?
>
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
(SELECT co.status , ROW_NUMBER() OVER (ORDER BY order_date DESC) rnum
SELECT trial_status, paid_status FROM test; Received on Wed Apr 13 2011 - 08:05:44 CDT
Date: Wed, 13 Apr 2011 06:05:44 -0700 (PDT)
Message-ID: <78dd00b8-8bd0-4c0a-bb26-20d44c6d7cc2_at_t19g2000prd.googlegroups.com>
On Apr 13, 4:16 am, Randolf Geist <mah..._at_web.de> wrote:
> On Apr 12, 9:52 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > 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?
>
> 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/1430226684http://www.amazon.com/Expert-Oracle-Practices-Database-Administration...
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; Received on Wed Apr 13 2011 - 08:05:44 CDT