|
|
|
Junior Member
 
Group: Forum Members
Last Login: 12/20/2007 1:59:10 PM
Posts: 11,
Visits: 2
|
|
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."
Matt
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 3:41:05 AM
Posts: 522,
Visits: 264
|
|
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
|
|
|
|
|
Junior Member
Group: Forum Members
Last Login: 12/20/2007 1:59:10 PM
Posts: 11,
Visits: 2
|
|
Thanks Dieter. As an Aside, while it did not work in Create AS, if I create the Table seperately, and Insert, it works fine...
Matt
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 7/26/2006 11:33:00 AM
Posts: 1,
Visits: 1
|
|
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;
|
|
|
|