Re: Getting two fields from the one table.

"Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message news:T7zub.58$rG.1893_at_nnrp1.ozemail.com.au...
> I use Golden to send SQLs to our Oracle database. Today's objective is to
> retrieve product data from the database. The product can have many EX GST or
> INC GST prices, and the prices are per colour. The following SQL will give
> me the EX GST per colour and the INC GST per colour. So if an EX & INC price
> is available for a particular colour, I get two records. What i'd like to
> get is one record showing BOTH the EX & INC prices for that colour.
>
> SQL:
>
> /* Place the Division code here */
> var DivCode string
> var DivCode = "CHARACTER"
>
> select ps.code "Price Schema", s.stylecode "Style", s.name "Description",
> clr.clrcode "Col Code",
> clr.clrname "Col Desc", sku.sizeval "Sizes",sz.sizeseq, kapn.apnnum "APN",
> p2.price "Price", seas.code "Season"
> from sprice_style p1, sprice_clrsku p2, style s, clr, sku, kapn, sizes sz,
> refcode ps, refcode seas, refcode divn
> where p1.styleidx=s.styleidx
> and p1.spidx=p2.spidx
> and p2.clridx=clr.clridx
> and clr.clridx=sku.clridx
> and sku.skuidx=kapn.skuidx(+)
> and sku.sizeidx=sz.sizeidx
> and ps.rcidx=p1.rcidx
> and seas.rcidx(+)=clr.ref1
> and divn.rcidx=s.ref1
> and sku.active=1
> and sku.sizeval is not null
> and (ps.code='RRP EX GST' or ps.code='WHL EX GST')
> and divn.code= :DivCode
> order by s.stylecode;
>
> Output of THIS SQL:
> WHL EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L | 50 |
> 9317326271078 | 17.3 | WI97
> RRP EX GST | 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L | 50 |
> 9317326271078 | 29.95 | WI97
>
> The SQL-output Goal:
> 0019B | BANANAS IN PYJAMAS DAISY WOMEN | 290 | NAVY | L | 50 | 9317326271078
> | 17.3 | 29.95 | WI97
>
> Having both prices in the same record. How can I change my SQL to do this?
>
> ---------------------------------
> If you need additional info, let me know.
>
>
> --
>
> Julian Milano
>
>
>

Hop onto http://asktom.oracle.com and search for "pivot"

Anurag Received on Tue Nov 18 2003 - 20:01:57 CST