How to pass a value returned from inner query to outer query

Question

I am using a nested query for fetching records within a certain radius. I want to order the response by distance and also pass the distance as one of the parameter in response. here is the query I a using for same.

select ofr.offerId,ofr.outlet_id,ofr.offer_title,ofr.offer_icon,ofr.offer_description,ofr.CategoryId,ofr.offer_terms,
    ofr.price_description,ofr.rating,ofr.isdeleted,ofr.minpoint_required,otl.shop_name,otl.shop_address,otl.shop_city,otl.shop_phone,otl.shop_icon,
    otl.shop_latitude,otl.shop_longitude,otl.shop_country,otl.shop_zip,distance
from pp.offers as ofr 
join pp.outlets as otl 
where ofr.outlet_id = otl.shop_id and   
MBRContains(GeomFromText(CONCAT('Polygon((',x1,' ', y1,',', x2,' ', y2,',', x3,' ', y3,',',x4,' ', y4,',', x1,' ', y1,'))')),otl.g)
        and match(offer_title,offer_description) against(searchText) 
order by (
    SELECT  glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(latitude,longitude)))), GeomFromText(astext(PointFromWKB(POINT(otl.shop_latitude,otl.shop_longitude)))))))*100 
    AS distance)
    LIMIT 300

But when trying to execute this I am getting back an error

unknown field distance

How can I return the distance calculated in the inner query within the response of the sql query?

Thanks

Answer 1

You cannot define variables in a subquery in the order by and expect to use them anywhere else. If I understand correctly, put the expression in the select and then refer to it in the order by:

select ofr.offerId, ofr.outlet_id, ofr.offer_title, ofr.offer_icon, ofr.offer_description, 
       ofr.CategoryId, ofr.offer_terms,
       ofr.price_description, ofr.rating, ofr.isdeleted, ofr.minpoint_required, otl.shop_name,
       otl.shop_address, otl.shop_city, otl.shop_phone, otl.shop_icon,
       otl.shop_latitude, otl.shop_longitude, otl.shop_country, otl.shop_zip,
       (glength(LineStringFromWKB(LineString(GeomFromText(astext(PointFromWKB(POINT(latitude,longitude)))),
        GeomFromText(astext(PointFromWKB(POINT(otl.shop_latitude,otl.shop_longitude)))))))*100 
        AS distance
from pp.offers as ofr join
     pp.outlets as otl
     on ofr.outlet_id = otl.shop_id
where MBRContains(GeomFromText(CONCAT('Polygon((',x1,' ', y1,',', x2,' ', y2,',', x3,' ', y3,',',x4,' ', y4,',', x1,' ', y1,'))')),otl.g)
        and match(offer_title,offer_description) against(searchText) 

order by distance
LIMIT 300