ColumnName is missing with error messages

Database

ColumnName is missing with error messages

ColumnName is missing with error messages

In Teradata Is there any way to get column name as well with the error message. For example I have a table

Create test(

column1 int,

column2 timestamp(0),

column3 timestamp(0),

column4 timestamp(0),

column5 char(20)); and i try to populate this table with another table which contains some datatype mismatch

insert into test

SELECT col_accno,col2_dt,col3_start_dt,col4_end_dt,col5_name

 FROM "STG_OUT".test_data

When select tries to insert a wrong row which contains mismatch, it does not return me the COLUMNNAME.

For Example, if time is coming wrong in some field, the error message does not mention column name, it just return

6760 : invalid timestamp

but which column is having problem remains unknown.

is there is any mathod to know the columnName ?

4 REPLIES
Enthusiast

Re: ColumnName is missing with error messages

I don't think that would be possible...

Enthusiast

Re: ColumnName is missing with error messages

Hi,

You can't find out which field would give the Invalid Timestamp. Instead, you have to select each field and try to do the cast function. 

SELECT cast(col2_dt as timestamp(0))
FROM "STG_OUT".test_data;

SELECT cast(col3_start_dt as timestamp(0))
FROM "STG_OUT".test_data;

SELECT cast(col4_end_dt as timestamp(0))
FROM "STG_OUT".test_data;

Please try to see the Datatype present in the Source Table 'test_date'. If the Datatype is a VARCHAR, then it should contain length of 26. To cast it to Timetsamp.

Enthusiast

Re: ColumnName is missing with error messages

Hi,

Yes there is a method to identify the mismatch column. It might be a slow process, but always worked for me.

You can use the following statment. 

SELECT

column1 ,

--column2 ,

--column3,

--column4,

--column5

FROM  test

UNION

SELECT 

col_accno,

--col2_dt,

--col3_start_dt,

--col4_end_dt,

--col5_name

 FROM "STG_OUT".test_data

Now uncommment the column one by one and you will get the column actually causing the mismatch. 

Khurram

Khurram

Re: ColumnName is missing with error messages

I have seen that functionality once in Teradata Administrator. But i dont remember how to enable that.