Re: Multiple selects in one

From: The Magnet <art_at_unsu.com>
Date: Thu, 11 Mar 2010 06:08:48 -0800 (PST)
Message-ID: <d174b6dd-91b1-4132-89af-4959a0cbdfa3_at_g4g2000yqa.googlegroups.com>



On Mar 10, 4:30 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Mar 10, 1:49 pm, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > I'm trying to write a query that will combine multiple rows from the
> > same table into one row.  That can be done with each column being a
> > SELECT statement.
>
> > However, in my case each column has different criteria and I'm running
> > into a problem where if any of the criteria from any of the queries is
> > false, the entire query returns nothing.
>
> > SELECT customer_id, customer_name, new_order_id, old_order_id.........
> > FROM (SELECT customer_id, customer_name, new_order_id
> >             FROM......
> >             WHERE........),
> >           (SELECT  old_order_id
> >             FROM......
> >             WHERE........)
>
> > Basically I'm applying separate criteria to each column.  How can I
> > allow any of the column queries to be false and just return NULL or
> > whatever instead of the entire query failing?
>
> It would be easier to work out if you supplied some create statements/
> test data, but maybe FULL OUTER JOIN as a self-join will get you
> there.  See FOJ examples in docs.  There's probably a nested table,
> associative array or similar PL solution, too, and maybe an analytic
> over a UNION ALL way.  http://boneist-oracle.livejournal.com/5996.htmlhttp://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1...
>
> jg
> --
> _at_home.com is bogus.http://guyharrison.squarespace.com/blog/2010/2/22/memory-management-f...

Yes, I was also looking at analytical functions.

The query works fine if both conditions on both SELECT statements are positive, but if one is negative, the entire query fails.

The actual query I've been trying to work with is:

  SELECT customer_id, non_ut_subscr_id, non_ut_product_id, non_ut_status, ut_subscr_id, ut_product_id, ut_status   FROM (SELECT customer_id, subscription_id non_ut_subscr_id, produst_id non_ut_product_id,

               SUM(status) OVER (PARTITION BY customer_id) non_ut_status

        FROM subscriptions
        WHERE customer_id = p_customer_id
          AND produst_id <> 204),
       (SELECT customer_id, subscription_id ut_subscr_id, produst_id
ut_product_id,
               SUM(status) OVER (PARTITION BY customer_id) ut_status
        FROM subscriptions
        WHERE customer_id = p_customer_id
          AND produst_id = 204);

I want it all in one line. So, that is what I am trying to work with. I'm sure it can be done, just have to figure out the exact syntax.

Thanks! Received on Thu Mar 11 2010 - 08:08:48 CST

Original text of this message