Archives of the TeradataForum

Message Posted: Wed, 20 Dec 2006 @ 14:16:31 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Mohd:

Teradata does not mix metaphors (OK, data types) in the same column. It will all be character, decimal or integer type - not both. Therefore, your answers are exactly the same (13963=13963.00) for all but the third row (0.00).

The difference is in the third row Terdata did exactly what you asked it to do. BTW - you asked it to do it the long way. For instance, when coalescing numeric data - use a number. I say this because '0' is not a number. It is a character zero - big difference. You are forcing Teradata to convert your '0' to 0. In this case it is only 5 times so not all that significant regarding the completion time, but if you do this a few million you will slow down your query. You might as well get into good habits early. Besides, I copied and pasted your Teradata query and it does not work due to unbalanced parentheses.

BTW (by the way), why are you asking it to return numeric data (SUM) as left justified? On the surface you are doing things that don't seem to make sense.

Regardless, it did exactly as you asked it to do. The first query and the query you wrote are not equivalent. In the first, it was not told to return a zero when the data is null. In the ANSI standard one you wrote for Teradata one you asked it to return a zero when the data was null (COALESCE). That is what Teradata did for you and that is not what you wanted. So, why ask it to do that?

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

	<Prev	Next>		<<First	<Prev	Next>	Last>>