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

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

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]

>>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