MySQL: IS NOT NULL checked on column with custom generator (alias)

Question

The stored procedure still works, but I only need entries withdist_calculated IS NOT NULL . When I use this condition in where clause, it shows an error #1054 - Unknown column 'dist_calculated' in 'where clause'. Witout where the sentence works well and returns NULL entries too:

entity_id   dist_calculated     
49              NULL
50              NULL
52              4460.615
51              4875.179

And I want to exclude NULL.

I tried WHERE dist_calculated IS NOT NULLand WHERE cpe.dist_calculated IS NOT NULLstill threw an error.

My stored procedure:

DELIMITER //
CREATE PROCEDURE get_close_childcares(IN latUser DECIMAL(15,6),IN lngUser DECIMAL(15,6) )
BEGIN
    /*Get 4 ids of closest childcares*/
    /*Outer query   
    @param : userLat, userLng, Current childcare lat,current childcare lng
    Note : Inner query returns lat , lng of Current product 176 : lat , 177: lng
    */
    SELECT cpe.entity_id , get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) AS dist_calculated
    FROM catalog_product_entity AS cpe
    WHERE dist_calculated IS NOT NULL
    ORDER BY dist_calculated ASC
    LIMIT 0,4;

END
//
DELIMITER ;

And the call to the stored procedure:

call get_close_childcares(19.992100,73.777000)

Thank.

Answer 1

MySQL allows column aliases to be used in GROUP BY and HAVING, but not in WHERE statements. Therefore you need to use the full definition in WHERE, for example.

SELECT cpe.entity_id , get_distance_in_miles_between_geo_locations(latUser,lngUser,
    (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
        WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
    ),
    (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
        WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
    )
) AS dist_calculated
FROM catalog_product_entity AS cpe
WHERE
get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) IS NOT NULL
ORDER BY dist_calculated ASC
LIMIT 0,4;

Answer 2

:

SELECT
    cpe.entity_id,
    get_distance_in_miles_between_geo_locations (
        latUser,
        lngUser,
        cpev_1.`value`,
        cpev_2.`value`
    ) AS dist_calculated
FROM
    catalog_product_entity cpe
    INNER JOIN catalog_product_entity_varchar cpev_1 ON cpev_1.entity_id = cpe.entity_id
        AND cpev_1.attribute_id = 176
    INNER JOIN catalog_product_entity_varchar cpev_2 ON cpev_2.entity_id = cpev.entity_id
        AND cpev_2.attribute_id = 177
ORDER BY
    dist_calculated ASC
LIMIT 0,4;  

Answer 3

CREATE PROCEDURE get_close_childcares(IN latUser DECIMAL(15,6),IN lngUser DECIMAL(15,6) )
BEGIN
    /*Get 4 ids of closest childcares*/
    /*Outer query   
    @param : userLat, userLng, Current childcare lat,current childcare lng
    Note : Inner query returns lat , lng of Current product 176 : lat , 177: lng
    */
    SELECT cpe.entity_id , get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) AS dist_calculated
    FROM catalog_product_entity AS cpe
    WHERE get_distance_in_miles_between_geo_locations(latUser,lngUser,
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 176)
        ),
        (SELECT cpev.value FROM catalog_product_entity_varchar AS cpev 
            WHERE (cpev.entity_id = cpe.entity_id AND cpev.attribute_id = 177)
        )
    ) IS NOT NULL
    ORDER BY dist_calculated ASC
    LIMIT 0,4;

END