Informix

 View Only
  • 1.  Common Table Expression not giving right output

    Posted 14 days ago
    Edited by Hugo Zambrano 14 days ago
    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 14 days ago
    Edited by Hugo Zambrano 14 days ago
    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
    ------------------------------