Problem with WHERE in temporary field

Question

Problem with WHERE in temporary field

Navigation

#1 by (2 votes)
#2 by (0 votes)

1

I have this query that calculates the distance between 2 points given certain coordinates.

The query runs well, the problem is the WHERE of the query, this is the error:

Error Code: 1054. Unknown column 'dist' in 'where clause'

If I run the query without the WHRE if it throws me the result I want the problem is the WHERE.

What am I doing wrong

SELECT p.*,
(
    6371 * 
    (2 *
        atan2(
                sqrt(
                        (
                            sin(
                                radians(
                                            (p.Punto_Latitude__c - 19.4326077) / 2
                                        )
                                )

                            *

                            sin(
                                radians(
                                            (p.Punto_Latitude__c - 19.4326077) / 2
                                        )
                                )
                        )

                        +

                        (
                            cos(
                                radians(19.4326077)
                                )

                            *

                            cos(
                                radians(p.Punto_Latitude__c)
                                )
                        )

                        *

                        (
                            sin(
                                radians(
                                            (p.Punto_Longitude__c - (-99.015725)) / 2
                                        )
                                )

                            *

                            sin(
                                radians(
                                            (p.Punto_Longitude__c - (-99.015725)) / 2
                                        )
                                )
                        )
                    ),

                    sqrt(1 -
                        (
                            sin(
                                radians(
                                            (p.Punto_Latitude__c - 19.4326077) / 2
                                        )
                                )

                            *

                            sin(
                                radians(
                                            (p.Punto_Latitude__c - 19.4326077) / 2
                                        )
                                )
                        )

                        +

                        (
                            cos(
                                radians(19.4326077)
                                )

                            *

                            cos(
                                radians(p.Punto_Latitude__c)
                                )
                        )

                        *

                        (
                            sin(
                                radians(
                                            (p.Punto_Longitude__c - (-99.015725)) / 2
                                        )
                                )

                            *

                            sin(
                                radians(
                                            (p.Punto_Longitude__c - (-99.015725)) / 2
                                        )
                                )
                        )
                    )

            )
    )
) as dist
FROM places as p
WHERE dist > 100

mysql sql

asked by Alberto Siurob 13.10.2017 в 00:41

source

2 answers

Change the color of the floating text in a TextInputLayout Time limit, asynchronous reproduction

score 2 · Answer 1

The error is correct. The dist field does not exist in the places table. In general, the databases that process the last are the AS, since they do not affect the result of a query.

Therefore, what you can do is something like this:

SELECT * 
FROM (todo tu select sin el where) 
WHERE dist > 100

score 0 · Answer 2

0

I already solved it, if it works, it's with HAVING instead of WHERE .

select p.*,
       (...) as dist
from places as p
having dist > 100

answered by 13.10.2017 в 00:56