Your Location is: Home > Sql

Oracle SQL Join an ID on a field with a list of IDs some with ranges

From: Britain/United Kingdom View: 1978 Pearl 

Question

First of all, its not my database and I cannot change it:)

This is an oracle database

I have a table with 2 ID fields code and subcode:

Table1
ID Code Subcode
1  300  010
2  400  050
3  300  060

I have another table with a code range field that has a comma separated list of codes however some have a range.

Table2
ID CodeRange 
1  300.020,300.040-300.060
2  300.000-300.020,400.010
3  300.180,400.010-400.100,500.010

I wish to do a join that would pull out:

Table1.ID Table2.ID
1         2
2         3
3         1

To note: The first number can change in the range IE 300.010-400.190

The direction I am heading is to combine the Code and subcode into one number like:

TO_NUMBER(CONCAT(CONCAT(Code, '.'),Subcode))

Then look at splitting the string in substrings(numbers) and comparing or using find_in_set or something of that nature but I cannot get there. The issue is The CodeRange has as many as 20 different ranges but each one varies in size.

Any help would be appreciated. Thanks

Best answer

I had a similar approach to @Barbaros. By splitting the CSVs in Table2 into their own rows, you can then split the ranges into a beginning/end range and the explicit values as the same value for beginning/end, then do a numeric comparison.

WITH
    table1 (ID, Code, Subcode)
    AS
        (SELECT 1, '300', '010' FROM DUAL
         UNION ALL
         SELECT 2, '400', '050' FROM DUAL
         UNION ALL
         SELECT 3, '300', '060' FROM DUAL),
    table2 (ID, CodeRange)
    AS
        (SELECT 1, '300.020,300.040-300.060' FROM DUAL
         UNION ALL
         SELECT 2, '300.000-300.020,400.010' FROM DUAL
         UNION ALL
         SELECT 3, '300.180,400.010-400.100,500.010' FROM DUAL)
  SELECT t1.id AS t1_id, t2.id AS t2_id
    FROM table1 t1
         JOIN
         (SELECT id,
                 REGEXP_SUBSTR (coderange,
                                '[^,]+',
                                1,
                                commas.COLUMN_VALUE)    AS codes
            FROM table2,
                 TABLE (
                     CAST (
                         MULTISET (
                                 SELECT LEVEL
                                   FROM DUAL
                             CONNECT BY LEVEL <= LENGTH (REGEXP_REPLACE (coderange, '[^,]+')) + 1)
                             AS SYS.OdciNumberList)) commas) t2
             ON (TO_NUMBER (t1.code || '.' || t1.subcode) BETWEEN TO_NUMBER (SUBSTR (t2.codes, 1, 7))
                                                              AND TO_NUMBER (
                                                                      NVL (SUBSTR (t2.codes, 9),
                                                                           SUBSTR (t2.codes, 1, 7))))
ORDER BY t1.id;

Another answer

You can use Hierarchical Query as

WITH Table2_ AS
(
SELECT ID, REGEXP_SUBSTR(CodeRange,'[^,]+',1,level) AS CodeRange
  FROM Table2
 CONNECT BY level <= REGEXP_COUNT(CodeRange,',') + 1
     AND PRIOR SYS_GUID() IS NOT NULL
     AND PRIOR ID = ID
 ORDER BY ID, level
)
SELECT t1.ID AS t1_ID, t2.ID AS t2_ID
  FROM Table2_ t2
  JOIN Table1 t1
    ON t1.Code BETWEEN TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR(CodeRange,'[^-]+'),'[^.]+'))
                   AND TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR(CodeRange,'[^-]+$'),'[^.]+'))
   AND t1.Subcode BETWEEN TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR(CodeRange,'[^-]+'),'[^.]+$'))
                      AND TO_NUMBER(REGEXP_SUBSTR(REGEXP_SUBSTR(CodeRange,'[^-]+$'),'[^.]+$'))               
 WHERE INSTR(CodeRange,'-')>0  
 ORDER BY t1_ID


 T1_ID  T2_ID
 -----  -----
 1      2
 2      3
 3      1

Demo

where

  • determined the substrings, which contains hyphen characters, for each CodeRange columns those are splitted by hyphens through use of REGEXP_SUBSTR() function

  • rather than concatenation use numeric comparison within the WHERE condition against getting unexpected results due to character-wise comparison.