Re: Getting two fields from the one table.

you will need to join to each "price schema" structure separately

your where clause is including two PS.CODES in the same record set, you need to retrieve the two codes, and related price data, by joining to the REFCODE table twice, once for each CODE, then joining from each of these to related tables

the DECODE, CASE and pivot suggestions are related but more appropriate for when working with aggregate data in a matrix output.

• mcs

"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
|
|
|
Received on Wed Nov 19 2003 - 07:25:54 CST