SQL question about FIRST FETCH

[jkmak69]

I am using this sql in my program.

select s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance from store_location s, zip_code z, ( select latitude, longitude from zip_code where zip_code = '92704' ) loc where s.zip_code = z.zip_code order by distance

Hhowever, if I modify the statement using city and adding fetch first 1 row only, I get a token error.

select s.name, s.address1, s.address2, s.city, s.state, s.zip_code, s.country, s.area_code, s.phone_number, s.products, round(SQRT(((4774.81*(z.latitude-loc.latitude)*(z.latitude-loc.latitude)) +(2809*(z.longitude-loc.longitude)*(z.longitude-loc.longitude)))),1) distance from store_location s, zip_code z, ( select latitude, longitude from zip_code where city = 'Los Angeles' fetch first 1 row only ) loc where s.zip_code = z.zip_code order by distance

can someone tell me what i'm doing wrong?

thanks,

john

 

[gregsimpson]

John,

your FETCH FIRST 1 ROW ONLY needs to go after all your WHERE predicates. Put it after the ORDER BY.

Cheers
Greg

 

[balajipn]

Hi,

Did you check what version of DB2 UDB you are using?. I guess the "FETCH FIRST N ROWS ONLY" syntax is supported from DB2 V7.0.

Cheers,
Balaji.