SQL Teradata evaluation order of case when then else

Question

I want to run this query on the official Teradata Express for VMware Player (TDE 15.00.01 SLES 10 for VMware (40GB) with Viewpoint):

SELECT 'MaxValue' column_name,
       COUNT("MaxValue") AS count_value,
       COUNT(DISTINCT("MaxValue")) AS count_dist_value,
       MIN("MaxValue") AS min_value,
       MAX("MaxValue") AS max_value,
       CASE WHEN max_value > 99999999999999 THEN 99999999999999
            ELSE SUM("MaxValue") END AS sum_value
FROM (SELECT TOP 100 * FROM "DBC"."IdCol") AS xy;

But I get this error:

Executed as Single statement. Failed [2616 : 22003] Numeric overflow occurred during computation. Elapsed time = 00:00:00.115

STATEMENT 1: Select Statement failed.

So my question is that why the ELSE statement is evaluated when the CASE logic is True? And how can I run this query? I want the COUNT, MIN, MAX, AVG, SUM etc. informations from unknown tables where I don't know if a column contains 20 digit long numbers or not. Thank you!

Don't know much about teradata, but maybe you just need to cast whatever value you are passing to SUM,MIN,MAX... into a wider datatype (INT => BIGINT?) to make it not overflow. (like you would do in SQL Server for example: stackoverflow.com/a/1222890/2186023) — DrCopyPaste

ragesz ragesz · Accepted Answer · 2015-06-18T11:21:25

Based on DrCopyPaste's comment, the solution is:

SELECT 'MaxValue' column_name,
       COUNT("MaxValue") AS count_value,
       COUNT(DISTINCT("MaxValue")) AS count_dist_value,
       MIN("MaxValue") AS min_value,
       MAX("MaxValue") AS max_value,
       CASE WHEN max_value > 99999999999999 THEN 99999999999999
            ELSE SUM(CAST("MaxValue" AS BIGINT)) END AS sum_value
FROM (SELECT TOP 100 * FROM "DBC"."IdCol") AS xy;

Thank you!

SQL Teradata evaluation order of case when then else

1 Answers