Getting two fields from the one table.

From: Prince Of Thieves <thief_NOSPAM_at_hotmail.com>
Date: Wed, 19 Nov 2003 11:55:37 +1100
Message-ID: <T7zub.58$rG.1893@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 Tue Nov 18 2003 - 18:55:37 CST