because the APN field in kkeyc doesn't contain the exact string
931732615128. In your select you use the substr function to obtain the APN,
I suspect you want where substr(k.apn,1,12)='931732615128'
--
Niall Litchfield
Oracle DBA
Audit Commission UK
"Prince Of Thieves" <thief_NOSPAM_at_hotmail.com> wrote in message
news:AY4Lb.58$Zi2.3343_at_nnrp1.ozemail.com.au...
> The following SQL runs fine:
>
> select s.stylecode "Stylecode", s.name "Description", c.clrcode "Colour
> Code", c.clrname "Colour Description",
> sku.sizeval "Sizes", substr(kapn.apnnum, 1, 12) "APN",
> LPAD(TO_CHAR(k.rrprice,'FM9999999990.00'),14,' ') "RRP"
> from kkeyc k, sku, clr c, style s, cust, kapn, sizes sz, sord ss, sordd
sd,
> sozd zz
> where k.skuidx=sku.skuidx
> and c.clridx=sku.clridx
> and s.styleidx=c.styleidx
> and ss.sordidx=sd.sordidx
> and sd.sorddidx=zz.sorddidx
> and zz.skuidx=sku.skuidx
> and k.custidx=cust.custidx
> and kapn.skuidx(+)=sku.skuidx
> and sku.sizeidx=sz.sizeidx
> and sku.active=1 and ss.active!=0 and sd.active!=0 and zz.active!=0
> and ss.typ=1
> and ss.num=228276
> order by s.stylecode, c.clrcode, sz.sizeseq;
>
> The results are:
>
> Stylecode, Description, Colour Code, Colour Description,
> Sizes, APN, RRP
> 7727A, COTTON PLAINDYE VINTAGE WASH, 300, ROYAL, XXL, 931732615147,
> 24.95
> 7727A, COTTON PLAINDYE VINTAGE WASH, 710, RED, S, 931732615128,
> 24.95
> 7727A, COTTON PLAINDYE VINTAGE WASH, 710, RED, M, 931732615129,
> 24.95
>
> (The commas are to help you line up the data)
>
> I now want to include a line in the SQL to limit the results to one APN
> only, thus I added the line:
>
> and k.apnnum = '931732615128'
>
> ....to the WHERE clause, but now I get nothing as a result. Why?
>
>
>
> --
>
> Prince Of Thieves
>
>
>
>
Received on Thu Jan 08 2004 - 03:13:37 CST
