Informix

 View Only
Expand all | Collapse all

Common Table Expression not giving right output

  • 1.  Common Table Expression not giving right output

    Posted Fri May 13, 2022 04:34 PM
    Edited by Hugo Zambrano Fri May 13, 2022 10:14 PM
    Does somebody knows why the following CTE does not work? In particular why SUBSTR(line, 1, 10) is giving me only one character and not ten?

    WITH t1 (line, mySplit) AS
     (SELECT "123456789 ASDFGHJKL 123456789 XCVBNMKLP ", ""
               UNION ALL
      SELECT SUBSTR(line, 11, LENGTH(line)-10), SUBSTR(line, 1, 10) FROM t1 WHERE LENGTH(line) > 0
              )
    SELECT * FROM t1;

    I expect to get the following in the second column:

    123456789
    ASDFGHJKL
    123456789
    XCVBNMKLP

    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------


  • 2.  RE: Common Table Expression not giving right output

    Posted Fri May 13, 2022 10:42 PM
    Edited by Hugo Zambrano Fri May 13, 2022 10:44 PM
    I just found the reason it was not working: I have to tell that the empty string in the initial subquery is actually 10 or more characters long, so I have to cast the empty string to the length I need, in this case to CHAR(10).

    WITH t1 (line, mySplit) AS
     (SELECT "123456789 ASDFGHJKL 123456789 XCVBNMKLP ", ""::CHAR(10)
               UNION ALL
      SELECT SUBSTR(line, 11, LENGTH(line)-10), SUBSTR(line, 1, 10) FROM t1 WHERE LENGTH(line) > 0
              )
    SELECT * FROM t1;

    or

    WITH t1 (line, mySplit) AS
     (SELECT "123456789 ASDFGHJKL 123456789 XCVBNMKLP ", CAST("" AS CHAR(10))
               UNION ALL
      SELECT SUBSTR(line, 11, LENGTH(line)-10), SUBSTR(line, 1, 10) FROM t1 WHERE LENGTH(line) > 0
              )
    SELECT * FROM t1;


    ------------------------------
    Zambrano, Hugo
    Informix DBA
    Ottawa Police
    Ottawa, ON
    (613)236-1222, 5575
    ------------------------------