Informix

 View Only
  • 1.  Creating a series from two char columns

    Posted Wed March 24, 2021 10:05 AM
    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


  • 2.  RE: Creating a series from two char columns
    Best Answer

    Posted Thu March 25, 2021 01:52 AM
    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
    ------------------------------



  • 3.  RE: Creating a series from two char columns

    Posted Thu April 22, 2021 08:52 AM
    Edited by System Fri January 20, 2023 04:33 PM
    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
    ------------------------------



  • 4.  RE: Creating a series from two char columns

    IBM Champion
    Posted Thu March 25, 2021 03:03 AM
    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
    ------------------------------