Search query

gistfile1.sql

DECLARE
    -- This variable will hold our query string
    l_query VARCHAR2(4000);
    
    -- The lat/long values of the postcode input
    l_lat VARCHAR2(100);
    l_lng VARCHAR2(100);
BEGIN
    -- Work out lat/long of input using Brian's procedure
    IF :P1_POSTCODE IS NOT NULL THEN
        brian.POSTCODE_TO_LAT_LNG_GM_API(:P1_POSTCODE, l_lat, l_lng);
    END IF;

    -- Construct the query, using the lat/long for distance calculations
    l_query := 'select "PRODUCTS"."PRODUCT_ID", "IMAGES"."IMAGE_ID", "NAME", "DESCRIPTION", "POSTCODE", "PRICE",
        dbms_lob.getlength("THUMBNAIL") "THUMBNAIL"';
    
    -- If we're searching by post code we need to select the distance also
    IF :P1_POSTCODE IS NOT NULL THEN
        l_query := l_query || ', ' || '
            TO_CHAR(SDO_GEOM.SDO_DISTANCE(
                SDO_GEOMETRY(
                    2001,
                    8307,
                    SDO_POINT_TYPE('|| l_lng ||', '|| l_lat ||', null),
                    null,
                    null
                ),
                "LOCATION",
                0.005,
                ''unit=mile''
            ), ''FM9999999990d099'') || '' miles'' "DISTANCE"
        ';
    ELSE
      l_query := l_query || ', ' || '
          NULL "DISTANCE"
      ';
    END IF;
    
    -- Append the FROM and LEFT JOIN clause to the query
    l_query := l_query || ' ' || '
        from "PRODUCTS"
        left join "IMAGES" on "PRODUCTS"."PRODUCT_ID" = "IMAGES"."PRODUCT_ID"
    ';

    -- Exclude archived products
    l_query := l_query || ' ' || '
        where (
            ARCHIVED = ''N''
        )
    ';

    -- If we have a search string add it to the query
    IF :P1_REPORT_SEARCH IS NOT NULL THEN
        l_query := l_query || ' ' || '
             and (
                CONTAINS(NAME, ''$' || :P1_REPORT_SEARCH || ''') > 0
            )
        ';
    END IF;

    -- If we're searching by postcode add distance ordering
    IF :P1_POSTCODE IS NOT NULL THEN
        IF :P1_ORDERING = 'closest' THEN
            l_query := l_query || ' ' || '
                order by "DISTANCE" asc
            ';
        ELSE
            l_query := l_query || ' ' || '
                order by "DISTANCE" desc
            ';
        END IF;
    END IF;

    -- Debug output
    -- htp.p(l_query);

    -- Return the query string
    RETURN l_query;
END;