I'm trying to create a table in SQL Assistant . I cannot create the table when i'm trying to format the column cust_phone in the example below. Is something wrong in the below example. Appreciate your help.
create table customer_phone
,cust_phone decimal(10,0) format '999-999-9999'
PRIMARY INDEX (cust_num);
Syntax is correct as mention by you
What error are you getting in creating a table with format,it will help in debugging
Which version of teradata you are running? I have tested it on 14 and the same statement works fine. Can please tell what error actually you are facing?
I changed the column to cust_phone INTEGER FORMAT '999-999-9999' but still not working. I'm using Teradata 13.0. I created the table and when I'm inserting the data through macros the phone number is displayed in its original format (ex., 1,236,541,111) But I should get 123-654-1111. The FORMAT which I have given in create table is not working.
Your format works fine while storing.
In SQL assistant the format won't work by default in select statment. We have to explicity convert the column to varchar.
In your case please try below SQL in sqlassistant -
select cust_num,cust_phone (varchar(15)),free_minutes from customer_phone;
In Bteq this conversion is not requried.
Thanks for all your responses. As you suggested that FORMAT is not working by default in sqlassistant. When I explicitly converted the column to varchar in select statement it is working fine.
Thanks for clarifying.
SQLA/ODBC converts the formats to local system's format. You can still view the actual data stored using BTEQ/CLI connectvity.