MySQL : IS NOT NULL check on custom generated column(alias)

Question

The stored procedure is working fine till now , but I want records only when dist_calculated IS NOT NULL. When I use that condition in where clause , it shows error #1054 - Unknown column 'dist_calculated' in 'where clause'. Witout where clause it runs well and returns NULL records too like :

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 NULL and WHERE cpe.dist_calculated IS NOT NULL still gives error.

My stored procedure is :

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 call to the stored procedure :

call get_close_childcares(19.992100,73.777000)

Thanks.

Answer 1

MySQL does allow use of the column aliases on GROUP BY and HAVING but not in WHERE statements. Therefore you need to use the full definition in WHERE, e.g.

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;