There is 3 cases: (In all cases value needs to be picked up based on priority)

• case 1 : zip exist between range
• case 2: zip does not exist between range
• case 3 : overlap range

Table
Temp1

Data

OUTPUT :

I am trying with below code , but It's not picking the data based on priority:

SELECT ZIP,ZIP_START,ZIP_END,VALUE,PRIORITY,STATE,IX FROM 
(
    SELECT TMP1.*,
    ROW_NUMBER () OVER (PARTITION  BY STATE,ZIP ORDER BY PRIORITY ) IX

    FROM 

        (
            WITH CASE_1 AS 
                (   SELECT 
                    temp1.*
                    ,DATA.ZIP 
                    FROM TEMP1 
                    LEFT JOIN
                    "DATA" ON DATA.STATE  = temp1.STATE 
                    WHERE DATA.ZIP BETWEEN TEMP1.ZIP_START AND TEMP1 .ZIP_END
                ),
                CASE_2 AS
                (
                    SELECT 
                    temp1.*
                    ,DATA.ZIP 
                    FROM "DATA" 
                    LEFT JOIN
                    TEMP1 ON DATA.STATE  = temp1.STATE 
                    WHERE  (ZIP_START IS NULL OR ZIP_START = '')
                    AND (ZIP_END IS NULL OR ZIP_END = '')
                    AND Not EXISTS 
                    (SELECT 1 FROM CASE_1 WHERE CASE_1.zip=DATA.zip
                    AND CASE_1.STATE=DATA.STATE)
                )
            SELECT * FROM CASE_1
            UNION
            SELECT * FROM CASE_2
        )TMP1
)   TMP2
WHERE TMP2.IX = 1;

CodePudding user response：

CREATE TABLE TEMP1 (
  STATE VARCHAR(10),
  ZIP_START NUMBER,
  ZIP_END NUMBER,
  PRIORITY NUMBER,
  VAL VARCHAR(10));


INSERT INTO TEMP1 VALUES ('NY', 100,200,1,'A');
INSERT INTO TEMP1 VALUES ('NY', 150,250,3,'C');
INSERT INTO TEMP1 VALUES ('NY', null,null,2,'B');


CREATE TABLE DATATABLE (
  STATE VARCHAR(10),
  ZIP NUMBER
 );

INSERT INTO DATATABLE VALUES ('NY', 201);
INSERT INTO DATATABLE VALUES ('NY', 400);

SELECT
t0.STATE,
t0.ZIP_START,
t0.ZIP_END,
CASE WHEN t0.COUNT_MATCH > 1 THEN 3
    WHEN t0.COUNT_MATCH = 1 THEN 1
    WHEN t0.COUNT_MATCH = 0 THEN 2 END AS PRIORITY,
t.VAL,
t0.ZIP
FROM
(
SELECT
  t1.STATE,
  MIN(t2.ZIP_START) AS ZIP_START,
  MAX(t2.ZIP_END) AS ZIP_END,
  COUNT(t2.STATE) AS COUNT_MATCH,
  t1.ZIP
FROM DATATABLE t1
  LEFT JOIN TEMP1 t2 ON (t1.STATE = t2.STATE AND t1.ZIP>=t2.ZIP_START AND t1.ZIP <= t2.ZIP_END)
GROUP BY
  t1.STATE, t1.ZIP) t0
LEFT JOIN TEMP1 t ON (t0.STATE = t.STATE AND CASE WHEN t0.COUNT_MATCH > 1 THEN 3
    WHEN t0.COUNT_MATCH = 1 THEN 1
    WHEN t0.COUNT_MATCH = 0 THEN 2 END = t.PRIORITY)
;

CodePudding user response：

From Oracle 12, you can use a LATERAL join and filter when the zip is within range or when one-or-other end of the range is NULL the ORDER BY priority and FETCH the FIRST matched ROW ONLY:

SELECT t.*, d.zip
FROM   data d
       CROSS JOIN LATERAL (
         SELECT *
         FROM   temp1 t
         WHERE  d.state = t.state
         AND    (t.zip_start <= d.zip OR t.zip_start IS NULL)
         AND    (t.zip_end   >= d.zip OR t.zip_end   IS NULL)
         ORDER BY priority
         FETCH FIRST ROW ONLY
       ) t

In earlier versions, you can join the two tables and then use the ROW_NUMBER analytic function to find the best match:

SELECT state, zip_start, zip_end, priority, value, zip
FROM   (
  SELECT t.*,
         d.zip,
         ROW_NUMBER() OVER (PARTITION BY d.ROWID ORDER BY t.priority) AS rn
  FROM   data d
         INNER JOIN temp1 t
         ON  (   d.state = t.state
             AND (t.zip_start <= d.zip OR t.zip_start IS NULL)
             AND (t.zip_end   >= d.zip OR t.zip_end   IS NULL))
)
WHERE  rn = 1;

Which, for the sample data:

CREATE TABLE Temp1 (state, zip_start, zip_end, Priority, Value) AS
SELECT 'NY', 100,  200,  1, 'A' FROM DUAL UNION ALL
SELECT 'NY', 150,  250,  3, 'c' FROM DUAL UNION ALL
SELECT 'NY', null, null, 2, 'B' FROM DUAL;

CREATE TABLE Data (state, zip) AS
SELECT 'NY', 201 FROM DUAL UNION ALL
SELECT 'NY', 400 FROM DUAL;

Both output:

STATE	ZIP_START	ZIP_END	PRIORITY	VALUE	ZIP
NY	null	null	2	B	201
NY	null	null	2	B	400

db<>fiddle here