Joining subquery from same table on 1=1 in Oracle I have a table with zip codes, latitude and longitude. I have a working query that takes a radius and latitude and longitude and returns all the zip codes within the required radius:

        SELECT zip.zip,
                ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(:clng, :clat, NULL), NULL, NULL)), (
                            SELECT geom
                            FROM standard_zip
                            WHERE zip = zip.zip
                            ), 0.005) / 1609.34, 2) AS dist
            FROM standard_zip zip
            WHERE zip.zip IN (
                    SELECT DISTINCT (zip) AS zip
                    FROM standard_zip c
                    WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5)) = 'TRUE'
                        AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(:clng, :clat, :r, 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
                    )
            ORDER BY DIST


Since the lat and long are already in the table I would like to provide a zip code as input for the query instead of having to do a separate lookup first, but this just seems to churn forever..

SELECT 
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
LEFT JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp on 1=1
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST


What am I doing wrong?



MguerraTorres suggested using a CTE, which sounds great. Here's what I tried:

WITH inp as (SELECT x as lng, y as lat from standard_zip where zip = :zip)
SELECT 
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
WHERE zip.zip IN (
        SELECT DISTINCT (zip) AS zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST


Problem is, Oracle says inp.lng is an invalid identifier. Why?
I think it should be this:

SELECT 
    zip.zip,
    ROUND(SDO_GEOM.SDO_DISTANCE((MDSYS.SDO_GEOMETRY(2001, 4326, MDSYS.SDO_POINT_TYPE(inp.lng, inp.lat, NULL), NULL, NULL)), (SELECT geom FROM standard_zip WHERE zip = zip.zip), 0.005) / 1609.34, 2) AS dist,
    inp.lat,
    inp.lng
FROM standard_zip zip
   CROSS JOIN (SELECT x as lng, y as lat from standard_zip where zip = :zip) inp
WHERE zip.zip IN (
        SELECT zip
        FROM standard_zip c
        WHERE SDO_FILTER(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5)) = 'TRUE'
            AND SDO_RELATE(c.geom, SDO_UTIL.CIRCLE_POLYGON(inp.lng, inp.lat, (:r * 1609.34), 5), 'mask=ANYINTERACT querytype = WINDOW') = 'TRUE'
        )
ORDER BY DIST;