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
------------------------------
Original Message:
Sent: Fri May 13, 2022 04:34 PM
From: Hugo Zambrano
Subject: Common Table Expression not giving right output
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