Re: Multiple selects in one
From: gazzag <gareth_at_jamms.org>
Date: Thu, 11 Mar 2010 06:21:28 -0800 (PST)
Message-ID: <148c859c-4731-48ed-851d-343d80c74c5d_at_z4g2000yqa.googlegroups.com>
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!
Date: Thu, 11 Mar 2010 06:21:28 -0800 (PST)
Message-ID: <148c859c-4731-48ed-851d-343d80c74c5d_at_z4g2000yqa.googlegroups.com>
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
Received on Thu Mar 11 2010 - 08:21:28 CST