Re: Multiple selects in one

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 12 Mar 2010 21:22:49 +0100
Message-ID: <4b9aa293$0$6730$9b4e6d93_at_newsspool2.arcor-online.net>

On 12.03.2010 09:11, Shakespeare wrote:

> 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

Nor i.
This sql has several problems.
1) Inline view

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

can be written as

select unique customer_id,subscription_id,produst_id,status from subscriptions
where customer_id = 565511633
and produst_id is not null

If there may be at most one record for both categories (produst_id=204 and all non 204) - then unique may be omitted

2) Expression like
LEAD(subscription_id, 1, NULL) OVER (PARTITION BY customer_id ORDER BY customer_id) ut_subscr_id,

is equal to LEAD(subscription_id, 1, NULL) OVER (PARTITION BY customer_id ORDER BY NULL) ut_subscr_id

which means, give me ANY subscription_id other than from actual row. In case, there are only 2 rows per customer_id - it may be with probability 50% either NULL or subscription_id from another row, in case of 3 records - 33% for any other row, and so on. LEAD or LAG require rows to be uniquely ordered, otherwise, result may be upredictable.

For that reason, row_number() may order the rows from the inline view randomly, so making randomly which one will be returned.

In general, i suppose, the pivoting method suggested by Randolf is more appropriate than using analytical functions for this query, because it does some kind of aggregation (from every group of 2 rows per customer will be returned one), so group by is on purpose...

Best regards

Maxim Received on Fri Mar 12 2010 - 14:22:49 CST