Problem in Cast

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