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?

Prince, You display the apn value from kapn which is outer joined to the result set so perhaps there is no matching apn value entry in the kkeyc table since the apn value is not one of your join conditions.

HTH -- Mark D Powell -- Received on Thu Jan 08 2004 - 07:38:38 CST