| 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.
go back to the drawing board -- based on VC's response, it looks like you
should pursue an inline view (from clause subquery) of your pricing tables,
with a decode to implement the pivot-style query (but make sure that your
tables are properly indexes and analyzed)
also, when you're having trouble getting expected results from even moderately complex queries, the best approach is to test the query 'from the inside out' -- that is, build it up from a simple query to a more complex query, testing the results each step of the way.
"Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message
news:Kdexb.95$zW.2766_at_nnrp1.ozemail.com.au...
| 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
| and divn.rcidx=s.ref1
| and (ps.code='RRP EX GST' or ps.code='WHL EX GST')
| /* Place the Division code here then press F7 while highlighting the SQL
*/
| and divn.code = 'CORPORATE'
| order by s.stylecode, clr.clrcode;
|
| 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...
| > 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
| >
|
> -------------------------------------------------- ---------- -----------| 1
| > --
| > > --------
| > > Ace Ski Boot 10012
> -------------------------------------------------- ---------- ---------- -175
| > --
| > > -------
| > > Ace Ski Boot 10012
> -------------------------------------------------- ---------- ---------- -175
| > --
| > > -------
| > > Ace Ski Boot 10012
 |
 |