FORMAT option in create table

Database
Enthusiast

FORMAT option in create table

Hi,

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);

Thanks

swapna

7 REPLIES
Highlighted
Enthusiast

Re: FORMAT option in create table

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

Enthusiast

Re: FORMAT option in create table

Change this to cust_phone INTEGER FORMAT '999-999-9999',

and let me know.

Cheers,

Enthusiast

Re: FORMAT option in create table

Hi,

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?

Khurram
Enthusiast

Re: FORMAT option in create table

Hi,

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

VALUES(:cust,:phone,:mins););

EXEC free_min (100,1236541111,60);

Thanks

Enthusiast

Re: FORMAT option in create table

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.

Enthusiast

Re: FORMAT option in create table

Hi,

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.

Teradata Employee

Re: FORMAT option in create table

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