Converting Range values in a column into Separate rows

General

Converting Range values in a column into Separate rows

Hi,

Could you please help me in converting Range values in a column into Separate rows

There is a table with three columns with below data values.






Col A Col B Col C
123 abc U7 38-40 GEORGE STREET

Col C is a combination of Unit number + Street number + Street name

If the Street number is a range value (as indicated above 38-40) it needs to split in sepearate row data values as below.






Col A Col B Col C
123 abc U7 38 GEORGE STREET
123 abc U7 39 GEORGE STREET
123 abc U7 40 GEORGE STREET

Thanks

2 REPLIES
Enthusiast

Re: Converting Range values in a column into Separate rows

This SQL could be your starting point converting the range values into separte rows and tune it according to your requirements...

CREATE SET TABLE tbl ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
col VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( col );

INSERT INTO tbl VALUES ( '7-17');
INSERT INTO tbl VALUES ( '33-39');
INSERT INTO tbl VALUES ( '1-10');

--TABLE WITH MIN AND MAX:

CREATE SET TABLE tbl2 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
col2 VARCHAR(10) CHARACTER SET LATIN NOT CASESPECIFIC,
_min INTEGER,
_max INTEGER)
PRIMARY INDEX ( col2 );

INSERT INTO tbl2
SEL col, SUBSTR(col,1,INDEX (col,'-')-1) _min, SUBSTR (col,INDEX(col,'-')+1,CHARS(col)) _max FROM tbl

--RECURSIVE QUERY:

WITH RECURSIVE cte
(col, _add, _min, _max) AS
(
SELECT -- Current value
col2,
0 AS _add,
_min,
_max
FROM tbl2
UNION ALL
SELECT -- Rest of Values
cte.col,
cte._min+1 AS _add,
cte._min+1,
cte._max
FROM cte JOIN tbl2 s
ON cte.col = s.col2
WHERE cte._min < cte._max
)
SELECT
cte.col,
cte._min AS _result
FROM cte JOIN tbl2 s
ON cte.col = s.col2
ORDER BY 1,2

Enthusiast

Re: Converting Range values in a column into Separate rows

Why it doesnt work for larger ranges e.g. 50000?