Re: Multiple selects in one

From: The Magnet <art_at_unsu.com>
Date: Thu, 11 Mar 2010 06:51:51 -0800 (PST)
Message-ID: <cef5d644-cc4c-4bb7-b4a6-731d510380ac_at_e1g2000yqh.googlegroups.com>



On Mar 11, 8:21 am, gazzag <gar..._at_jamms.org> wrote:
> On 11 Mar, 14:08, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > 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!
>
> Why not do as Joel suggested and post some DDL to create the relevant
> objects and some DML to populate the tables with test data.  I
> guarantee a far quicker response!  Oracle version would be good too.
>
> HTH
> -g

Sorry, version 10g R2.

As for DDL / DML, not sure why that helps. Just a table with a couple of records:

SUBSCRIPTIONS


CUSTOMER_ID         12345
PRODUCT_ID          203
SUBSCRIPTION_ID     6767
STATUS              2

CUSTOMER_ID         12345
PRODUCT_ID          204
SUBSCRIPTION_ID     6768
STATUS              1

Now, to get those into 1 display record:

CUSTOMER_ID PRODUCT_ID SUBSCRIPTION_ID STATUS PRODUCT_ID SUBSCRIPTION_ID STATUS

12345        203         6767             2       204
6768             1

And if one side is not true:

CUSTOMER_ID PRODUCT_ID SUBSCRIPTION_ID STATUS PRODUCT_ID SUBSCRIPTION_ID STATUS

12345                                             204
6768             1
Received on Thu Mar 11 2010 - 08:51:51 CST

Original text of this message