UDA
Enthusiast

## Problem in Cast

Hi,
I have a table in which say COL1 is DECIMAL(11,0).
When am trying to unload into a file as CHAR(11), am facing problem.
After unloading, few of the rows in the file have a period at the end. Am trying as :

SEL
TRIM(CAST COL1 AS CHAR(11))
from
tableA

Should i need to use FORMAT?? How can i go about that?
Any help

7 REPLIES
Enthusiast

## Re: Problem in Cast

This should work as long as you don't have negative numbers:

SEL
TRIM(CAST (CAST(COL1 AS FORMAT 'ZZZZZZZZZZ9') AS CHAR(11)))
from
tableA

If you have negative numbers, replace all of the 'Z' characters with a '-' in the format clause.

Enthusiast

## Re: Problem in Cast

No, i dont have any negative numbers.But still getting such way.Is it because of junk values/non displayable values?
As of now, i have fixed by coding as:

Sel
Case
When Index (cast (col1 As char (11)), '.')=length (trim (cast (col1 As char (11)))) Then

cast(substr(cast(col1 As char(11)),1,length(trim(cast(col1 As char(11))))-1) As CHAR(11))

Else

cast(substr(cast(col1 As char(11)),1,length(trim(cast(col1 As char(11))))) As CHAR(11))

End

FROM tablea

GROUP BY

col1

but this is not a generic one and not advisable
Enthusiast

## Re: Problem in Cast

Barry's code should have done the trick, is the column type really DECIMAL(11,0) or something else like CHAR(11) ?

you can also try this

SEL TRIM((DECCOL11 (INTEGER , FORMAT 'ZZZZZZZZZZ9')) (CHAR(11)))
FROM MYTABLE;
Enthusiast

## Re: Problem in Cast

I got it...
First i formatted the column as FORMAT '99999999999' and then casted as CHAR(11).

But my doubt is since the field is decimal(11,0) whats the need to format as 99999999999.Can any one explain this?

Also, can anyone give me their persoanl id who is master in teradata SQL?

Enthusiast

## Re: Problem in Cast

The default format for DECIMAL(11,0) is '-----------9.' and that was the reason why you were getting the dot (.) at the end. So by explicitly specifying the formatting clause, we got rid of the '.'

you can do a

SELECT FORMAT(TBLNAME.COLNAME);

to get the default formatting of a column.
Enthusiast

## Re: Problem in Cast

Thanks for info.
Could any one please provide me the default format for all the datatypes in teradata?
Enthusiast

## Re: Problem in Cast

You will get all the details in "Data types and Literals" manual, chapter 9