Informix

Expand all | Collapse all

Creating a series from two char columns

  • 1.  Creating a series from two char columns

    Posted 26 days ago
    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
    ------------------------------


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

    Posted 25 days ago
    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


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

    Posted 25 days ago
    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