Re: Getting two fields from the one table.

From: VC <boston103_at_hotmail.com>
Date: Thu, 27 Nov 2003 00:00:42 GMT
Message-ID: <J8bxb.313446$HS4.2773419@attbi_s01>

Hello Mark.

What follows below is not intended to criticize your solution. Just a couple of remarks:

Given:

/* Items */
create table t1 as select object_name name, object_id id from all_objects where rownum <=25000;
/* Prices */
create table t2 as select id, class, round(dbms_random.value*100) price from   (select rownum class from user_objects where rownum <=2) b,   (select object_id id from all_objects where rownum <=25000) a

/* Join solution */
select t1.name, a.price, b.price
from t1, t2 a, t2 b
where t1.id=a.id and a.class=1

    and t1.id=b.id and b.class=2

/* Pivot solution */
select name,price1, price2
from t1,
(select id,

        max(decode(class, 1, price, null)) price1,
        max(decode(class, 2, price, null)) price2

 from t2 group by id) t2
where t1.id=t2.id

1. The 'pivot' approach is more performant because the number of LIOs is about 50% lower in comparison to the 'join' solution (set autottrace on). The performance advantage would be even higher if one wanted to 'flatten' more than two prices because one would need to join the prices table as many times as there are different prices per item.
2. It's not necessary to 'group by ' all the select columns -- one needs to flatten only the price table (see above).

Rgds.

"mcstock" <mcstockspamplug_at_spamdamenquery.com> wrote in message news:AvednZSabc91PFmiRVn-ig_at_comcast.com...
> "Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message
> news:N5Wwb.73$wh7.3507_at_nnrp1.ozemail.com.au...
> | Thanks McStock & VC.
> |
> | I can't get it to work. What should the resulting SQL look like?
> |
> | --
> |
> | Julian Milano
> |
> |

>

> here's the basic concept:

>

> instead of this:

>

> SQL> select
> 2 prod.name
> 3 , prod.id
> 4 , prc.price_class
> 5 , prc.price
> 6 from
> 7 s_product prod
> 8 , s_price prc
> 9 where
> 10 prc.product_id = prod.id
> 11 order by
> 12 prod.name
> 13 , prc.price_class
> 14 /
> ...

>


> NAME                                                       ID PRICE_CLASS

> PRICE
> -------------------------------------------------- ---------- -----------

--


> --------

> Ace Ski Boot                                            10012           1

> 175

> Ace Ski Boot                                            10012           2

> 178.5

> Ace Ski Pole                                            10022           1

> 21.95

> Ace Ski Pole                                            10022           2

> 23.05

> Alexeyer Pro Lifting Bar                                40421           1

> 85

> Alexeyer Pro Lifting Bar                                40421           2

> 77.35

> Alomar Glove                                            50418           1

> 75

> Alomar Glove                                            50418           2

> 95.25

> ...

>
>


> do this:

>


>   1  select

>   2    prod.name

>   3  , prod.id

>   4  , prc1.price as CLASS1

>   5  , prc2.price as CLASS2

>   6  from

>   7    s_product prod

>   8  , s_price prc1

>   9  , s_price prc2

>  10  where

>  11    prc1.price_class = 1

>  12    and

>  13    prc1.product_id = prod.id

>  14  and

>  15    prc2.price_class = 2

>  16    and

>  17    prc2.product_id = prod.id

>  18  order by

>  19*   prod.name

> SQL> /

> ...

>


> NAME                                                       ID     CLASS1

> CLASS2

> -------------------------------------------------- ---------- ---------- -

--


> -------

> Ace Ski Boot                                            10012        175

> 178.5

> Ace Ski Pole                                            10022      21.95

> 23.05

> Alexeyer Pro Lifting Bar                                40421         85

> 77.35

> Alomar Glove                                            50418         75

> 95.25

> ...

>
>
>


> here's the DECODE version someone suggested:

>


> SQL> select

>   2    prod.name

>   3  , prod.id

>   4  , max(

>   5    decode(

>   6      prc.price_class

>   7     ,1,prc.price

>   8     ,null

>   9     )

>  10    ) as CLASS1

>  11  , max(

>  12    decode(

>


>  13      prc.price_class

>  14     ,2,prc.price

>  15     ,null

>  16     )

>  17    ) as CLASS2

>  18  from

>  19    s_product prod

>  20  , s_price prc

>  21  where

>  22    prc.product_id = prod.id

>  23  group by

>  24    prod.name

>  25  , prod.id

>  26  /

> ...

>


> NAME                                                       ID     CLASS1

> CLASS2

> -------------------------------------------------- ---------- ---------- -

--


> -------

> Ace Ski Boot                                            10012        175

> 178.5

> Ace Ski Pole                                            10022      21.95

> 23.05

> Alexeyer Pro Lifting Bar                                40421         85

> 77.35

> ...

>
>


> with the decode, you artificially must select the max of each prices, and

> you must include all other columns in the GROUP BY -- which can then get

> quite large

>
>


> again, the reference suggested earlier is http://asktom.oracle.com, search

> for PIVOT (which is a concept, not a SQL keyword or function)

>


> -- Mark Stock

>
>
>

Received on Wed Nov 26 2003 - 18:00:42 CST