and here is the error 2620 : The format or data contains a bad character
If I'm tying to concatenate with 'e' or ',' it works. I presume it's considering my attributes as numeric then. e.pla_rw_place_number_ and m.pla_rw_place_number are of type VARCHAR(1000). What should I add to get this working ?
In your CASE expression, ideally the datatypes of all the alternative expressions (WHEN/ELSE clauses) should match. If not, Teradata must try to guess which data type you intended for the result, and attempt to implicitly CAST the others to that type.
Note also that a CAST of the final result (around the entire CASE / END) will only be applied after the expression has been evaluated.
WITH RECURSIVE RECTABLE(pla_pnr_message_group_id_, pla_rw_place_number_, ligne) AS ( SELECT m.pla_pnr_message_group_id, m.pla_rw_place_number, m.ligne FROM test_view m WHERE m.ligne = 1 UNION ALL SELECT e.pla_pnr_message_group_id_, CASE WHEN CAST(substr(e.pla_rw_place_number_,char(e.pla_rw_place_number_)-3,4) as integer) = CAST(m.pla_rw_place_number as integer) - 1 THEN CASE WHEN substr(e.pla_rw_place_number_,char(e.pla_rw_place_number_)-3,1) = ',' THEN SUBSTR(e.pla_rw_place_number_, 0, CHARACTER_LENGTH(e.pla_rw_place_number_)-3) || ',' || m.pla_rw_place_number ELSE e.pla_rw_place_number_ || ',' || m.pla_rw_place_number END ELSE e.pla_rw_place_number_ || 'A' || m.pla_rw_place_number END AS place, m.ligne FROM RECTABLE e, test_view m WHERE m.ligne = e.ligne + 1 AND m.pla_pnr_message_group_id = e.pla_pnr_message_group_id_ ) SELECT r.pla_pnr_message_group_id_, r.pla_rw_place_number_ FROM RECTABLE r;
CAST(substr(e.pla_rw_place_number_,char(e.pla_rw_place _number_)-3,4) as integer) in the recursive part will fail if the previous pla_rw_place _number_ had less than 4 charcters, e.g. "SELECT CAST (12A4 AS INT)"
If you replace 'A' with ',' or 'e' it's not failing because 'e' is used in scientific notation and ',' (= thousand separator) is simply removed. Try a SELECT CAST('12e4' AS INT), CAST('12,4' AS INT)
What are you actually trying to achieve? Looks like searching for consecutive values, this might probably be achieved more efficiently using OLAP-functions. Could you post some DDL/Inserts and the required output?