ReadVitamin

Why do Asterisk/star sign appear in your output/result set in Teradata.

Written by admin in Monday, June 25th 2007 under IT

This is a very common problem while displaying some numbers (numeric values) using the format function in Teradata. Instead of the numbers, you would see a bunch of wild char characters (‘*’); If you run the following SQL in Teradata SQL Assistant Version 7.1

SELECT cast ((545458874489 (format ‘999-999-9999′)) as char (15)) as Format_Cast_PhoneNum

Output would be something like follows;

Format_Cast_PhoneNum

***********

This happens because of the number of digits presented (12 in this case) to the format function is more than the number of symbol digits (10 in this case) or format characters available in the format command.

Again,Number of digits presented is 12 è 545458874489

Number of Symbolic character is 10 è 999-999-9999 (excluding the dash character)

Hence, you get error in your output.

How to solve this? Just make sure that the number of Symbolic character/digit is always equal or greater than the number of digits presented for formatting/conversion. The above SQL can be written in as follows to get a correct result.

SELECT cast ((545458874489 (format ‘99-999-999-9999′)) as char (15)) as Format_Cast_PhoneNum

Popularity: 19% [?]