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.

vhu · Accepted Answer · 2015-07-24T06:34:20.907

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;

Can you please let me know `what could be done here then `? – Pratik Joshi Jul 24 '15 at 06:33 — Pratik Joshi, Jul 24 '15 at 06:33

Avidos · Answer 2 · 2015-07-24T07:00:46.467

You can try this mate:

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;

score 0 · Answer 3 · answered Jul 24 '15 at 06:35

Please try

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

Not working , generating Stored procedure for it gives error — Pratik Joshi, Jul 24 '15 at 06:36

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

3 Answers3