Creating a series from two char columns
Hugo Zambrano wanted to expand a table of ranges stored as from/to character columns (such as 1 to 4, or a to c) into one row per value. SangGyu Jeong supplied a recursive CTE with two chained WITH tables that generates the sequence per code. Henri Cujass suggested an SPL returning with resume. Hugo confirmed SangGyu's query gave exactly what he needed.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I had a table (let's call it ranges) where I had separated a-c values into two columns with respective values a, c.
1027 1-4 1 4
2562 a-c a c
2564 1-3 1 3
6663 a-c a c
7426 a-d a d
I want to generate a table of this sort:
1027 1
1027 2
1027 3
1027 4
2562 a
2562 b
2562 c
2564 1
2564 2
2564 3
etc.
I was thinking that perhaps this CTE could help but I don't know how to incorporate it, to get inputs from columns:
WITH temp (ltr, s) AS
( SELECT 'a', 1
UNION ALL
SELECT CHR(ASCII(ltr) + s), s FROM temp WHERE ltr < 'c'
)
SELECT * FROM temp;------------------------------
Zambrano, Hugo
Informix DBA
Ottawa Police
Ottawa, ON
(613)236-1222, 5575
------------------------------
#Informix
Hi Hugo,
I've modified your query a bit. Is this the desired result?
WITH temp1 (code, st, en) AS
(
SELECT 1027, '1', '4'
UNION ALL
SELECT 2562, 'a', 'c'
UNION ALL
SELECT 2564, '1', '3'
UNION ALL
SELECT 6663, 'a', 'c'
UNION ALL
SELECT 7426, 'a', 'd'
),temp2 (code, ltr, s) AS
(
SELECT * FROM temp1
UNION ALL
SELECT code, CHR(ASCII(ltr) + 1), s FROM temp2 WHERE ltr < s
)
SELECT code, ltr FROM temp2;------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------
Hi Hugo, nice task. Maybe a small SPL which includes the range-expansion algorithmic , return with resume, can work. about as: select hc_range_expansion(c_number,c_from,c_to) from my_range_table where .... Regards Henri ------------------------------ Henri Cujass leolo IT, CTO Germany ------------------------------
Woo! That was great! That's what I need. I also learned that you could have more than one temp table the second one using the result of the first one within this WITH construct. Thanks a lot. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------