varchar concatenation

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 ?

Re: varchar concatenation

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.

Re: varchar concatenation

2 sec ago

What's the datatype of m.pla_rw_place_number?

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?