Re: Multiple selects in one

From: The Magnet <art_at_unsu.com>
Date: Thu, 11 Mar 2010 08:49:14 -0800 (PST)
Message-ID: <dfbe38ff-48c4-47fb-a4c6-f91840cda02c_at_f8g2000yqn.googlegroups.com>

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; Received on Thu Mar 11 2010 - 10:49:14 CST