Re: SQL should work!!?? But it doesn't.

"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?

Perhaps because there is no record with that value? The selected value of '931732615128' is obtained by the expression substr(kapn.apnnum, 1, 12), so maybe the apnnum value is actually '931732615128XYZAAAA!!123123213qds' or something. Try changing the WHERE clause to:

...
and kapn.apnnum like '931732615128%' Received on Thu Jan 08 2004 - 04:37:13 CST