Re: Subquery

From: Randolf Geist <>
Date: Wed, 13 Apr 2011 02:16:46 -0700 (PDT)
Message-ID: <>

On Apr 12, 9:52 pm, The Magnet <> 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.


Oracle related stuff blog:

Co-author of the "OakTable Expert Oracle Practices" book: Received on Wed Apr 13 2011 - 04:16:46 CDT

Original text of this message