All Forums Database
sriswapna 9 posts Joined 12/13
29 Jan 2014
FORMAT option in create table

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_num int
,cust_phone decimal(10,0) format '999-999-9999'
,free_minutes int
PRIMARY INDEX (cust_num);

khatriprad 12 posts Joined 12/10
29 Jan 2014

Hi Swapna,
Syntax is correct as mention by you
What error are you getting in creating a table with format,it will help in debugging


Raja_KT 1246 posts Joined 07/09
29 Jan 2014

Change this to cust_phone INTEGER FORMAT '999-999-9999',
and let me know.



Raja K Thaw
My wiki:
Street Children suffer not by their fault. We can help them if we want.

M.Saeed Khurram 544 posts Joined 09/12
30 Jan 2014

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?


sriswapna 9 posts Joined 12/13
30 Jan 2014

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. 

CREATE MACRO free_min (cust INT, phone INT, mins INT) AS

(UPDATE customer_phone SET free_minutes = :mins

WHERE customer_num = :cust AND phone_num = :phone

ELSE INSERT INTO customer_phone



EXEC free_min (100,1236541111,60);



sgarlapa 88 posts Joined 03/13
30 Jan 2014

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.

sriswapna 9 posts Joined 12/13
30 Jan 2014

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.

Adeel Chaudhry 773 posts Joined 04/08
31 Jan 2014

SQLA/ODBC converts the formats to local system's format. You can still view the actual data stored using BTEQ/CLI connectvity.

-- If you are stuck at something .... consider it an opportunity to think anew.

You must sign in to leave a comment.