Re: Multiple selects in one

From: The Magnet <art_at_unsu.com>
Date: Fri, 12 Mar 2010 11:03:24 -0800 (PST)
Message-ID: <d1c68214-1c60-49b8-af77-06a32697f3da_at_33g2000yqj.googlegroups.com>

On Mar 12, 12:13 pm, joel garry <joel-ga..._at_home.com> wrote:
> On Mar 12, 8:29 am, The Magnet <a..._at_unsu.com> wrote:
>
>
>
> > On Mar 12, 6:19 am, Randolf Geist <mah..._at_web.de> wrote:
>
> > > On Mar 11, 3:51 pm, The Magnet <a..._at_unsu.com> wrote:
>
> > > > Sorry, version 10g R2.
>
> > > > As for DDL / DML, not sure why that helps.  Just a table with a couple
> > > > of records:
>
> > > > SUBSCRIPTIONS
> > > > -------------
> > > > CUSTOMER_ID         12345
> > > > PRODUCT_ID          203
> > > > SUBSCRIPTION_ID     6767
> > > > STATUS              2
>
> > > > CUSTOMER_ID         12345
> > > > PRODUCT_ID          204
> > > > SUBSCRIPTION_ID     6768
> > > > STATUS              1
>
> > > > Now, to get those into 1 display record:
>
> > > > CUSTOMER_ID  PRODUCT_ID  SUBSCRIPTION_ID  STATUS  PRODUCT_ID
> > > > SUBSCRIPTION_ID  STATUS
> > > > 12345        203         6767             2       204
> > > > 6768             1
>
> > > > And if one side is not true:
>
> > > > CUSTOMER_ID  PRODUCT_ID  SUBSCRIPTION_ID  STATUS  PRODUCT_ID
> > > > SUBSCRIPTION_ID  STATUS
> > > > 12345                                             204
> > > > 6768             1
>
> > > I think what you're looking for is commonly called a PIVOT operation
> > > that turns rows into columns. Oracle 11g introduced the PIVOT
> > > operator, but it can be done with pre-11g versions as well by using a
> > > GROUP BY with a bit cumbersome aggregate function expression.
>
> > > Something like this should give you a starting point:
>
> > > with subscriptions as (
> > > select
> > >          12345 as CUSTOMER_ID
> > >        , 203   as PRODUCT_ID
> > >        , 6767  as SUBSCRIPTION_ID
> > >        , 2     as STATUS
> > > from
> > >          dual
> > > union all
> > > select
> > >          12345 as CUSTOMER_ID
> > >        , 204   as PRODUCT_ID
> > >        , 6768  as SUBSCRIPTION_ID
> > >        , 1     as STATUS
> > > from
> > >          dual
> > > )
> > > 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
> > >               , max(decode(product_id, 204, null, subscription_id)) as
> > > non_ut_subscr_id
> > >               , max(decode(product_id, 204, null, product_id))      as
> > > non_ut_product_id
> > >               , max(decode(product_id, 204, null, status))          as
> > > non_ut_status
> > >               , max(decode(product_id, 204, subscription_id, null)) as
> > > ut_subscr_id
> > >               , max(decode(product_id, 204, product_id, null))      as
> > > ut_product_id
> > >               , max(decode(product_id, 204, status, null))          as
> > > ut_status
> > >         FROM
> > >                 subscriptions
> > >         WHERE
> > >                 customer_id = 12345
> > >         GROUP BY
> > >                 customer_id
> > >         )
> > > ;
>
> > > If you're interested what 11g offers, you can lookup the new PIVOT
> > > syntax in this excellent article by OakTable fellow Adrian Billington:http://www.oracle-developer.net/display.php?id=506
>
> > > Regards,
> > > Randolf
>
> > > Oracle related stuff blog:http://oracle-randolf.blogspot.com/
>
> > > Co-author of the "OakTable Expert Oracle Practices" book:http://www.apress.com/book/view/1430226684http://www.amazon.com/Exper......
>
> > Interesting query.  It works.  I never really understood the 'WITH'
> > queries.  Reading on it did not really help either.
>
> > Thanks.
>
> I'm not sure if you are saying you understand it now, but basically it
> is just a way of manufacturing whatever data you want, whether
> literally as Randolf did, or from other table data, as in the article
> he pointed at.
>
> Not sure if the UNION part of your "beast" was exactly what you meant
> to post, couldn't see the difference between =204 and <>204, maybe I'm
> just cross-eyed.
>
> jg
> --
> _at_home.com is bogus.
> Certainly there are lying liars who lie about certs:http://www.signonsandiego.com/news/2010/mar/11/lax-oversight-of-crede...

Just trying to get in one record whether or not they have any product <> 204 and if they have a product = 204. I figure the UNION will give me max 2 records, and then I can use LEAD to get the values from the second record into the first record. Received on Fri Mar 12 2010 - 13:03:24 CST