ReadVitamin

Data Conversion/interpretations in any computer language is as essential as in any human language. As we see a sea of platforms and languages in IT industry it becomes a necessity to learn the way they can communicate universally.

In Teradata most of the conversions are done by CAST function, formatting or FORMAT function comes into picture while you want to present them without actually truncating the data; hence it is preferred in ANSI. Just remember that CAST the data only when you need it. There is a lot of memory put in use to perform the CAST operation specially if there are large amount of data. Conversions in Teradata again done in the following ways;

1. Implicit

2. Explicit using the CAST function.

3. Explicit using Teradata conversion syntax.

First let’s look at the implicit one.

When data a conversion happens without using the CAST function explicitly, it is implicit conversion. In Teradata, it permits the assignment and comparison of some types (Byte, Numeric, CHAR, VARCHAR, DATE, TIME and TIMESTAMP) without requiring them to be explicitly converted.

Scenario

If you have a column with a CHAR data type and there is a numeric value present in there and you want to use that value to update another CHAR field or a numeric field, you can do that with out any explicit conversion as follows.

Table definition:

CREATE VOLATILE TABLE Salary

(Name CHAR (10),

Salary INTEGER,

Dept Integer,Start_Year CHAR (4))

ON COMMIT PRESERVE ROWS;

With the above table definition, following statement would not need any explicit casting and it would not result in any error. But again this is not the right way of doing it.

UPDATE salary SET start_year = start_year + 1

WHERE dept = 100

Next is the “Explicit” conversion which needs the explicit usage of the CAST function, basic syntax for CAST is as follows, which can be used with the SELECT statement as well as with the WHERE clause.

CAST (<column name>) AS <data type (length)>

Using the same in a SELECT statement;

SELECT CAST (<column name1> AS INTEGER),

CAST (<column name2> AS DATE)

FROM tablename

WHERE column name3 = CAST (<column name1> AS INTEGER);

In case of “Explicit conversion using Teradata conversion syntax” which is an extension to the ANSI standard, this is retained only for backward compatibility with existing applications and is not encouraged. Use CAST to explicitly convert data types. Syntax;

(Column name (data type (length)));

Example:-

SELECT ‘10000′ (SMALLINT);

Popularity: 28% [?]