Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Query to identify the Decimal
 
From:   McCall, Glenn David

  Is there a possibility of writing a sql in teradata such that the query fails when a particular criteria is not satisfied?  


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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016