Creating volatile table results in

Database

Creating volatile table results in

I am able to run the following query fine and get data back:

 

WITH MYTABLE as (select distinct ITEM
, cast(FIELDA || '-' || FIELDB as varchar(21)) as CONCAT_FIELD
from MYDB
)
select ITEM
, trim(trailing ',' from
cast(xmlagg(CONCAT_FIELD || ',' ORDER BY CONCAT_FIELD) AS VARCHAR(10000))) as CONCATENATED_DATA
from MYTABLE
group by 1

 

However when I try to create a volatle table:

CREATE VOLATILE TABLE PART_MODEL AS (

Same code as above)

WITH DATA NO PRIMARY INDEX
ON COMMIT PRESERVE ROWS

;

I get the error: "[Teradata Database] [5355] The arguments of the CAST function must be of the same character data type."

 

When I comment out the ", trim(trailing ',' from 
cast(xmlagg(CONCAT_FIELD || ',' ORDER BY CONCAT_FIELD) AS VARCHAR(10000))) as CONCATENATED_DATA"

lines the volatile table creates fine. I don't know why it is complaining about the cast function only when creating a volatile table. 

 

Does anyone know how I might debug it?

 

Thanks in advance.

1 REPLY
Highlighted
Teradata Employee

Re: Creating volatile table results in

The issue is that CREATE TABLE in Teradata mode is implicitly trying to make the VARCHAR column CHARACTER SET LATIN by default (for the "create table" part of the request), but CAST from XML to VARCHAR requires CHARACTER SET UNICODE (for the "insert data" part of the request). 

 

If you explicitly define the character set in the SELECT, it should work within CREATE TABLE AS:

WITH MYTABLE as (select distinct ITEM
, cast(FIELDA || '-' || FIELDB as varchar(21)) as CONCAT_FIELD
from MYDB
)
select ITEM
, trim(trailing ',' from
cast(xmlagg(CONCAT_FIELD || ',' ORDER BY CONCAT_FIELD) AS VARCHAR(10000) CHARACTER SET UNICODE)) as CONCATENATED_DATA
from MYTABLE
group by 1