Create Table - Problem with Data Types

Database

Create Table - Problem with Data Types

Ok, first off, I am VERY new to SQL. I'm trying to pick it up, and have essentially been learning thru my own mistakes, which has worked so far, but now I'm stumped. I'm sure there's an easy fix for this statement, but nothing has worked for me thus far.

I have the following SQL and am trying to create a new "Summary" table from another table by pulling out all the distinct account numbers, and then creating 7 custom columns. I don't have any problem with the last 4 "Flag" ones, but the first 3 are giving me issues. For "Promo_Type" I'm getting the "...expected something like ) between the word 'Promo_Type' and the 'varchar' keyword.

CREATE TABLE FINANCE_USER_TBLS.roo579_Q1P_02_Temp_Daily_List AS (

SELECT DISTINCT DA.ACCT_NO,
Promo_Type varchar(10),
Acct_Date DATE FORMAT 'YYYY-MM-DD',
New_or_Existing varchar(10),
'N' AS R1_Flag,
'N' AS DP_Flag,
'N' AS HSI_Flag,
'N' AS Video_Flag

FROM FINANCE_USER_TBLS.roo579_Q1P_01_Activity DA

)

WITH DATA PRIMARY INDEX(ACCT_NO);

Now I do have a worksround where I change the lines to something like "'xxxxxxxxxx' AS Promo_Type", but would like to be a little more specific. Plus I figure it would be good to learn these things.
2 REPLIES
Teradata Employee

Re: Create Table - Problem with Data Types

Teradata database supports two different cast syntax:

a) CAST(Promo_Type as varchar(10))
b) Promo_type (VarChar(10))

Re: Create Table - Problem with Data Types

Hi FIFARay007,

The way you defined the datatypes is wrong.You should have casted it instead like below:

CREATE TABLE FINANCE_USER_TBLS.roo579_Q1P_02_Temp_Daily_List AS (

SELECT DISTINCT DA.ACCT_NO,
CAST(Promo_Type AS varchar(10)) PROMO_TYPE,
CAST(Acct_Date AS DATE FORMAT 'YYYY-MM-DD') Acct_Date,
CAST(New_or_Existing AS varchar(10)) New_or_Existing,
'N' AS R1_Flag,
'N' AS DP_Flag,
'N' AS HSI_Flag,
'N' AS Video_Flag

FROM FINANCE_USER_TBLS.roo579_Q1P_01_Activity DA

)

WITH DATA PRIMARY INDEX(ACCT_NO);

Hope this works !!!
Cheers:-)