Create Volatile Table not working with Trim

Analytics
Enthusiast

Create Volatile Table not working with Trim

I am trying to create a volatile table
with the following slection criteria.

CREATE VOLATILE TABLE DSL3 AS
(

Select BTN (CHAR(13)) BTN,

Case
When substr(
trim(BILLED_CITY_STATE)
,(length ( trim(BILLED_CITY_STATE))-1)
,2
)
In ('AL','AK','AS','AZ','AR','CA','CO','CT','DE','DC','FM','FL','GA','GU','HI','ID','IL','IN','IA','KS','KY','LA','ME','MH','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','MP','OH','OK','OR','PW','PA','PR','RI','SC','SD','TN','TX','UT','VT','VI','VA','WA','WV','WI','WY','AE','AA','AE','AE','AE','AP')
Then substr(
trim(BILLED_CITY_STATE)
,1
,(length ( trim(BILLED_CITY_STATE))-2)
)
Else BILLED_CITY_STATE
End
(Char(19)) Billing_City,

Case
When substr(
trim(BILLED_CITY_STATE)
,(length ( trim(BILLED_CITY_STATE))-1)
,2
)
In ('AL','AK','AS','AZ','AR','CA','CO','CT','DE','DC','FM','FL','GA','GU','HI','ID','IL','IN','IA','KS','KY','LA','ME','MH','MD','MA','MI','MN','MS','MO','MT','NE','NV','NH','NJ','NM','NY','NC','ND','MP','OH','OK','OR','PW','PA','PR','RI','SC','SD','TN','TX','UT','VT','VI','VA','WA','WV','WI','WY','AE','AA','AE','AE','AE','AP')
Then substr(
trim(BILLED_CITY_STATE)
,(length ( trim(BILLED_CITY_STATE))-1)
,2
)
Else ' '
End
(Char(2)) Billing_State,

FROM TableX

Group By 1,2,3

)
WITH DATA
PRIMARY INDEX (BTN)
ON COMMIT PRESERVE ROWS;

The Select statement works without the create table, but when added it gives the error "Syntax error: expected something between '(' and the 'trim' keyword."
3 REPLIES
Senior Apprentice

Re: Create Volatile Table not working with Trim

Hi Matt,
it's not the TRIM it's LENGTH:
This is no Teradata SQL, it's ODBC SQL, so replace it with CHAR_LENGTH and your query will run.

The reason why it worked without the volatile table is an option in QueryMan:
Tools -> Options -> Query -> "Allowing Use of ODBC SQL Extensions in Queries"

If it's checked you can use ODBC SQL extensions like RTRIM()/MONTH()/LENGTH() and the ODBC driver will replace it with valid Teradata SQL. Apparently this is not done if you submit a CREATE TABLE AS...

It's usually recommended to uncheck that option, because your query may not run on another PC with different settings and will never run using CLI.

Dieter
Enthusiast

Re: Create Volatile Table not working with Trim

Thanks Dieter. As an Aside, while it did not work in Create AS, if I create the Table seperately, and Insert, it works fine...

Re: Create Volatile Table not working with Trim

hi

it's not the trim that wasn't working, it was the length.

ansi syntax vs teradata syntax is the issue usually in these instances.

see examples below, CHARS worked where LENGTH did not.

THIS WORKED !

drop table dsl3;
create volatile table dsl3 as
(select trim(' HELLO ') (CHAR(20)) as test1)
with data
on commit preserve rows;

THIS DIDN'T WORK !

drop table dsl3;
create volatile table dsl3 as
(select trim(' HELLO ') (CHAR(20)) as test1
,length (trim(' HELLO ')) (BYTEINT) as test2)
with data
on commit preserve rows;

THIS WORKED !

drop table dsl3;
create volatile table dsl3 as
(select trim(' HELLO ') (CHAR(20)) as test1
,chars (trim(' HELLO ')) (BYTEINT) as test2)
with data
on commit preserve rows;