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

"Tony" <andrewst_at_onetel.net.uk> wrote in message news:c0e3f26e.0401080237.35a1d895_at_posting.google.com...
| "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%'

+ 2 cents:

the LIKE syntax would be preferable (assuming APNNUM is a varchar2, which it seems to be if you're using SUBSTR to display it) since it would allow the optimizer to use an ordinary index -- the SUBSTR syntax would require a matching function-based index

• mcs