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
------------------------------
Original Message:
Sent: Wed March 24, 2021 10:05 AM
From: Hugo Zambrano
Subject: Creating a series from two char columns
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