All Forums Database
pierrehenri 4 posts Joined 03/10
05 Mar 2010
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 ?


monisiqbal 119 posts Joined 07/09
05 Mar 2010

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

Fred 1096 posts Joined 08/04
05 Mar 2010

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.

pierrehenri 4 posts Joined 03/10
08 Mar 2010

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 !

dnoeth 4628 posts Joined 11/04
08 Mar 2010

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



monisiqbal 119 posts Joined 07/09
08 Mar 2010

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

pierrehenri 4 posts Joined 03/10
09 Mar 2010

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
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
ELSE e.pla_rw_place_number_ || 'A' || m.pla_rw_place_number
END AS place,
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_

dnoeth 4628 posts Joined 11/04
09 Mar 2010

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?



You must sign in to leave a comment.