| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Getting two fields from the one table.
Thanks again mcstock & VC.
Here's my original SQL:
select s.stylecode "Style", s.name "Description", clr.clrcode "Col Code",
clr.clrname "Col Desc", p2.price "Price"
from sprice_style p1, sprice_clrsku p2, style s, clr, refcode ps,refcode
divn
where p1.styleidx=s.styleidx
and p1.spidx=p2.spidx and p2.clridx=clr.clridx and ps.rcidx=p1.rcidx
Which produces:
Style Description Col Code Col Desc Price
2148T VIVA SPORTS POLYESTER TIE 290 NAVY 0 2148T VIVA SPORTS POLYESTER TIE 290 NAVY 7.9 2148T VIVA SPORTS POLYESTER TIE 860 GOLD 0 2148T VIVA SPORTS POLYESTER TIE 860 GOLD 9.5
And here's my attempt to follow mcstock's notes:
select s.stylecode "Style", s.name "Description", clr.clrcode "Col Code",
clr.clrname "Col Desc", prc1.price as class1, prc2.price as class2
from sprice_style p1, sprice_clrsku prc1, sprice_clrsku prc2, style s, clr,
refcode ps, refcode divn
where
(ps.code='RRP EX GST' and p1.spidx = prc1.spidx and prc1.clridx=clr.clridx)
and
(ps.code='WHL EX GST' and p1.spidx = prc2.spidx and prc2.clridx=clr.clridx)
and p1.styleidx=s.styleidx
and ps.rcidx=p1.rcidx
and divn.rcidx=s.ref1
and divn.code = 'CORPORATE'
order by s.stylecode, clr.clrcode;
from which I can't get any data out.
What have I done wrong?
-- Julian Milano "VC" <boston103_at_hotmail.com> wrote in message news:J8bxb.313446$HS4.2773419_at_attbi_s01...Received on Wed Nov 26 2003 - 21:35:22 CST
> 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
> >
> >
> >
>
>
 |
 |