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

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

So many possibilities, so little documentation provided.

Using Oracle?

You display substr(kapn.apnnum, 1, 12)

You limit by k.apnnum = '931732615128'

How do we even know you have a row that meets the criteria? There is no evidence provided that it must work.

Greg Received on Thu Jan 08 2004 - 07:14:56 CST