Oracle SQL Join an ID on a field with a list of IDs some with ranges
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:
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
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;
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
determined the substrings, which contains hyphen characters, for each
CodeRangecolumns those are splitted by hyphens through use of
rather than concatenation use numeric comparison within the WHERE condition against getting unexpected results due to character-wise comparison.