varchar concatenation

Database

varchar concatenation

Hi everyone,

I've got some issues with concatenation, here is the code sample :

... ELSE e.pla_rw_place_number_ || 'A' || m.pla_rw_place_number

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 ?

Thanks
7 REPLIES
Enthusiast

Re: varchar concatenation

try casting your integers to varchar like:
cast(num as varchar(20))
Teradata Employee

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

This is really perturbing,

I tried to cast my attributes as VARCHAR and CHAR, I tried to cast 'A' as well, I tried to replace 'A' by CHAR(65), it's still not working ...

This CASE WHEN expression is used inside a WITH RECURSIVE view, can it be the reason why ?

Thanks for your help !
Senior Apprentice

Re: varchar concatenation

Could you post the full CASE expression?
Thus it's much easier to see why it's actually failing.

Dieter
Enthusiast

Re: varchar concatenation

CASE runs fine inside the WITH RECURSIVE queries. As Dieter said, full query would elaborate things.

Re: varchar concatenation

Sure it's better with some sample :

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;
Senior Apprentice

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?

Dieter