Re: Multiple selects in one

From: joel garry <joel-garry_at_home.com>
Date: Fri, 12 Mar 2010 10:13:15 -0800 (PST)
Message-ID: <9d41005b-e726-42aa-a7bb-e021f7470af8_at_u5g2000prd.googlegroups.com>

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-credential-claims/

Received on Fri Mar 12 2010 - 12:13:15 CST