Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  Common Table Expression not giving right output

    Posted 05/13/22 04:34 PM
    Edited by System Admin 01/20/23 04:41 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
    ------------------------------
    #Informix


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

    Posted 05/13/22 10:42 PM
    Edited by System Admin 01/20/23 04:37 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
    ------------------------------



Related Content