Archives of the TeradataForum

Message Posted: Thu, 11 Apr 2008 @ 00:52:27 GMT

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

Are you sure you want the whole query to fail? When this happens there is no easy way of seeing why it failed, how many rows caused it to fail or what the valid values are.

For example if you had the following data:

     ID
     1
     2
     3
     0
     4
     5
     6

And ran this query:

     select case when col.id <>0.0 then cast(col.id as float) else
     failure from above_data;

All you would get is an error - you wouldn't see the non-zero data.

Have you considered the alternative of replacing the 0 values with nulls?

For example

     Select cast (nullifzero(c1) as float) from above_data;

This will still return the non-zero values with the 0 being replaced by null.

Assuming you really do want the whole query to fail if there is a zero value, can you explain why?

Surely deliberately causing a query to fail, presumably due to an invalid value (0), is back to front. Wouldn't it be better to prevent the invalid value (0) from being in the table in the first place (e.g. use a check constraint).

Nevertheless I, and I'm sure others are curious as to why you are trying to do this????

All the best

Glenn Mc

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