Prev: ORA-00470: LGWR process terminated with error
Next: New Procedurs With Version# In Name
From: gazzag on 11 Mar 2010 10:40
On 11 Mar, 14:51, The Magnet <a...(a)unsu.com> wrote:
>
> Sorry, version 10g R2.
>
> As for DDL / DML, not sure why that helps.  
<snip>

It helps because it will allow people to simply run the supplied
scripts to create the table and populate them without having to create
the test-case all by themselves.

HTH
-g
From: The Magnet on 11 Mar 2010 11:49
On Mar 11, 8:21 am, gazzag <gar...(a)jamms.org> wrote:
> On 11 Mar, 14:08, The Magnet <a...(a)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;
From: Shakespeare on 12 Mar 2010 03:11
Op 11-3-2010 17:49, The Magnet schreef:
> On Mar 11, 8:21 am, gazzag<gar...(a)jamms.org> wrote:
>> On 11 Mar, 14:08, The Magnet<a...(a)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
From: Maxim Demenko on 12 Mar 2010 15:22
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...(a)jamms.org> wrote:
>>> On 11 Mar, 14:08, The Magnet<a...(a)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

From: joel garry on 12 Mar 2010 16:22
On Mar 12, 12:22 pm, Maxim Demenko <mdeme...(a)gmail.com> wrote:
> 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...(a)jamms.org> wrote:
> >>> On 11 Mar, 14:08, The Magnet<a...(a)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

When I was trying to grok this, I came up with the same thing, then I
remembered there are some odd cases where making apparently redundant
subquery calls can trick the optimizer into a better plan. I wasn't
sure if it the OP had stumbled upon something like that, so that was
part of why I held back saying it. Another part was seeing "produst"
and some odd use of commas in the original actual query, so I was
thinking maybe something lost in translation.

Whatever works. This has been educational.

jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/2010/feb/21/be-careful-when-using-retirement-calculators/
First  |  Prev  | 
Pages: 1 2
Prev: ORA-00470: LGWR process terminated with error
Next: New Procedurs With Version# In Name