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
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 1Received on Thu Mar 11 2010 - 08:51:51 CST