Issues with EXPORT OUTPUT DATE FIELD is not coming correctly

Tools & Utilities
Enthusiast

Issues with EXPORT OUTPUT DATE FIELD is not coming correctly

I have some issues with TD Export command. The SQL I use in export command is

SELECT BUSINESS_UNIT
,' | ',EFFDT
,' | ',EFF_STATUS
FROM ABC

The output of this is spooled to a file called ABC.txt.

$ head ABC.txt
01454 | 06/0 | A |
01731 | 06/0 | A |
G0360 | 06/0 | A |
01700 | 06/0 | A |
01810 | 06/0 | A |
01500 | 06/0 | A |

When I run the query in query man, I get correct data. Result set attached below

01454 | 01/01/2006 | A |
01731 | 01/01/2006 | A |
G0360 | 01/01/2006 | A |
01700 | 01/01/2006 | A |
01500 | 01/01/2006 | A |

Interestingly, when I check DBC.COLUMNS, I find that this date field is has a column width 4.

ColumnName ColumnType ColumnLength
BUSINESS_UNIT CV 5
EFFDT DA 4
EFF_STATUS CV 1

Code Snippet of the BTEQ script as follows

/****************************************************************************************
Set the export file as table name with extension as .txt
****************************************************************************************/
.SET FULLYEAR ON;
.EXPORT DATA FILE=$TABLE_NAME.txt

/****************************************************************************************
Run the select statement in the file created by shell script
****************************************************************************************/

. run file $COL_FILE_NAME
.SET FORMAT OFF;
.EXPORT RESET
2 REPLIES
Teradata Employee

Re: Issues with EXPORT OUTPUT DATE FIELD is not coming correctly

Hello,

Integer-date has a length 4, there is no issue with that. Try by casting it to CHAR/VARCHAR, that should solve the issue.

HTH!

Regards,

Adeel
Enthusiast

Re: Issues with EXPORT OUTPUT DATE FIELD is not coming correctly

I would not be able to cast it as the SQL is generated dynamically. I thought to cast it to char with length based on column length from the table, but since I see that it is 4, that is also had to to be dropped.