Re: Multiple selects in one
From: joel garry <joel-garry_at_home.com>
Date: Fri, 12 Mar 2010 13:22:09 -0800 (PST)
Message-ID: <c4da5665-7be9-493d-ab37-f419261760d3_at_g8g2000pri.googlegroups.com>
On Mar 12, 12:22 pm, Maxim Demenko <mdeme..._at_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..._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
Date: Fri, 12 Mar 2010 13:22:09 -0800 (PST)
Message-ID: <c4da5665-7be9-493d-ab37-f419261760d3_at_g8g2000pri.googlegroups.com>
On Mar 12, 12:22 pm, Maxim Demenko <mdeme..._at_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..._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
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
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/feb/21/be-careful-when-using-retirement-calculators/Received on Fri Mar 12 2010 - 15:22:09 CST