Informix

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
------------------------------

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
------------------------------

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
------------------------------

Original Message:
Sent: Thu March 25, 2021 01:51 AM
From: SangGyu Jeong
Subject: Creating a series from two char columns

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
------------------------------

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
------------------------------

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
------------------------------