Re: Subquery
Date: Wed, 13 Apr 2011 02:16:46 -0700 (PDT)
Message-ID: <7f013667-8eef-4114-8741-125272831607_at_i4g2000vbq.googlegroups.com>
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/1430226684 http://www.amazon.com/Expert-Oracle-Practices-Database-Administration/dp/1430226684 Received on Wed Apr 13 2011 - 04:16:46 CDT