All Forums Analytics
MattW At ATT 11 posts Joined 06/06
23 Jun 2006
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 DATAPRIMARY 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."

dnoeth 4628 posts Joined 11/04
23 Jun 2006

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

Dieter

MattW At ATT 11 posts Joined 06/06
23 Jun 2006

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

virtuallyours 1 post Joined 05/06
26 Jun 2006

hiit'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 dataon 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 dataon 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 dataon commit preserve rows;

You must sign in to leave a comment.