Archives of the TeradataForum

Message Posted: Fri, 23 Nov 2007 @ 16:11:09 GMT

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

Mistry, Jeetendra wrote:

I'd guess that the error code was actually 3800:

     sel null T, cast(T as timestamp(6)) TT, coalesce(TT, current_timestamp);
       *** Failure 3800 Datatype Mismatch in THEN/ELSE expression.
                      Statement# 1, Info =0
       *** Total elapsed time was 1 second.

As Dieter and Ulrich already pointed out the problem comes from the slight mismatch in data types of the COALESCE arguments: the first one is the timestamp(6) without Time Zone, the second one is with TZ.

In case of such mismatches COALESCE (or rather the CASE construct behind it) will try to use the data type of the first argument and cast the second type but the cast apparently fails.

Mismatch in other data types can produce a wealth of different error messages:

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel null N, cast(N as char(1)) NC, coalesce(NC,1);
     sel null N, cast(N as char(1)) NC, coalesce(NC,1);
       *** Failure 3530 Invalid FORMAT string 'X(1)'.
                      Statement# 1, Info =0
       *** Total elapsed time was 1 second.

     ...

     sel null N, cast(N as INT) NI, coalesce(NI, '1');
     sel null N, cast(N as INT) NI, coalesce(NI, '1');
       *** Failure 3527 Format string '-(10)9' has combination of  numeric, charac
       ter  and GRAPHIC values.
                      Statement# 1, Info =0
       *** Total elapsed time was 1 second.

Sometimes slight mismatches do not lead to a failure and COALESCE will try to guess the type but these guesses do not seem to be super consistent so it's always worth checking whether the resulting data type matches your expectations:

     sel type('A');
     sel type('A');

       *** Query completed. One row found. One column returned.
       *** Total elapsed time was 1 second.

     Type('A')
     ---------------------------------------
     VARCHAR(1) CHARACTER SET UNICODE

       BTEQ -- Enter your DBC/SQL request or BTEQ command:
     sel null N, cast(N as char(2)) NC2, coalesce(NC2, 'A') CLSC, type(CLSC);
     sel null N, cast(N as char(2)) NC2, coalesce(NC2, 'A') CLSC, type(CLSC);

       *** Query completed. One row found. 4 columns returned.
       *** Total elapsed time was 1 second.

                N  NC2  CLSC  Type(CLSC)
     -----------  ---  ----  ------------------
                ?  ?    A     VARCHAR(2)

Victor

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