Re: Multiple selects in one

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 12 Mar 2010 09:11:52 +0100
Message-ID: <4b99f75e$0$22944$e4fe514c_at_news.xs4all.nl>

Op 11-3-2010 17:49, The Magnet schreef:

> 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

>
> Well, finally came up with this beast.  It works, so, I'll go with it:
>
> 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, status non_ut_status,
>               LEAD(subscription_id, 1, NULL) OVER (PARTITION BY
> customer_id ORDER BY customer_id) ut_subscr_id,
>               LEAD(produst_id, 1, NULL) OVER (PARTITION BY customer_id
> ORDER BY customer_id) ut_product_id,
>               LEAD(status, 1, 0) OVER (PARTITION BY customer_id ORDER
> BY customer_id) ut_status,
>               ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY
> customer_id) rnum
>        FROM (SELECT customer_id, subscription_id, produst_id, status
>              FROM subscriptions
>              WHERE customer_id = 565511633
>                AND produst_id<>  204
>              UNION
>              SELECT customer_id, subscription_id, produst_id, status
>              FROM subscriptions
>              WHERE customer_id = 565511633
>                AND produst_id = 204))
> WHERE rnum = 1;

Man, I sure hope I never have to do any maintenance on code like this....

Shakespeare Received on Fri Mar 12 2010 - 02:11:52 CST