Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 4 of 4 total |
#700 error SQL |
Fri, Sep 6 2019 5:22 PM | Permanent Link |
KimHJ Comca Systems, Inc | I can't see anything wrong with this SQL statement:
SELECT name, ( 3959 * acos( cos( radians(26.1420) ) * cos( radians( Cast(Latitude,float) ) ) * cos( radians( Cast(Longitude,float) ) - radians(-81.7948) ) + sin( radians(37) ) * sin( radians( Cast(Latitude,float) ) ) ) ) AS distance FROM StoreLocation HAVING distance < 10 ORDER BY distance; But I get a error: ElevateDB Error #700 An error was found in the statement at line 4 and column 8 (Invalid expression "distance" < 10 found, GROUP BY clause required for any column references) Thanks, For any Help Kim |
Fri, Sep 6 2019 9:08 PM | Permanent Link |
Raul Team Elevate | On 9/6/2019 5:22 PM, KimHJ wrote:
> I can't see anything wrong with this SQL statement: > > SELECT name, ( 3959 * acos( cos( radians(26.1420) ) * cos( radians( Cast(Latitude,float) ) ) * > cos( radians( Cast(Longitude,float) ) - radians(-81.7948) ) + sin( radians(37) ) * > sin( radians( Cast(Latitude,float) ) ) ) ) AS distance FROM StoreLocation > HAVING distance < 10 ORDER BY distance; > > But I get a error: > ElevateDB Error #700 An error was found in the statement at line 4 and column 8 (Invalid expression "distance" < 10 found, GROUP BY clause required for any column references) > > Thanks, > For any Help The error is correct and point you in the right direction. HAVING is only supported together with "group by" - you do not have a "group by" Raul |
Sat, Sep 7 2019 5:47 AM | Permanent Link |
Fernando Dias Team Elevate | KimHJ,
Raul is correct - HAVING can only be used with aggregate values, with GROUP BY. The correct way to apply a filter here is with WHERE condition, like SELECT <...> WHERE distance < 10 . however, because "distance" is not the name of a column in the base table "StoreLocation", you will have to repeat the expression, or in alternative, use a nested query: Option 1: (repeating the expression) --------- SELECT name, ( 3959 * acos( cos( radians(26.1420) ) * cos( radians( Cast(Latitude,float) ) ) * cos( radians( Cast(Longitude,float) ) - radians(-81.7948) ) + sin( radians(37) ) * sin( radians( Cast(Latitude,float) ) ) ) ) AS distance FROM StoreLocation WHERE ( 3959 * acos( cos( radians(26.1420) ) * cos( radians( Cast(Latitude,float) ) ) * cos( radians( Cast(Longitude,float) ) - radians(-81.7948) ) + sin( radians(37) ) * sin( radians( Cast(Latitude,float) ) ) ) ) < 10 ORDER BY distance Option 2 --------- (using nested selects) SELECT name, distance FROM ( SELECT name, ( 3959 * acos( cos( radians(26.1420) ) * cos( radians( Cast(Latitude,float) ) ) * cos( radians( Cast(Longitude,float) ) - radians(-81.7948) ) + sin( radians(37) ) * sin( radians( Cast(Latitude,float) ) ) ) ) AS distance FROM StoreLocation ) AS _TmpTbl WHERE distance < 10 ORDER BY distance -- Fernando Dias [Team Elevate] |
Sun, Sep 8 2019 12:02 AM | Permanent Link |
KimHJ Comca Systems, Inc | >>Fernando Dias wrote:
Raul is correct - HAVING can only be used with aggregate values, with GROUP BY. The correct way to apply a filter here is with WHERE condition, like SELECT <...> WHERE distance < 10 . however, because "distance" is not the name of a column in the base table "StoreLocation", you will have to repeat the expression, or in alternative, use a nested query:<< Thanks Fernando, I have this SQL in my windows application using the MySQL so I was surprised when it didn't work in EDB. Kim |
This web page was last updated on Tuesday, April 30, 2024 at 03:55 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |