Archives of the TeradataForum

Message Posted: Wed, 06 Dec 2006 @ 22:19:13 GMT

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

Hmmm, on my system select coalesce (100,0) * 100; generates an error. My system is:

     V2R.06.00.00.03
     06.00.00.03

I wonder if they've fixed (what I always thought was) a really annoying bug.

Anyway, it helps to think of coalesce not so much as a function (like min, max, sin etc) but as a case statement.

As in:

     Select case
                when p1 is not null then p1
                when p2 is not null then p2
                else null
            end * 100;

The above sort of looks like it needs parenthesis around the case statement don't you think?

Interestingly on my system if I substitute p1 and p2 for 100 and 0 respectively the above query works (without parenthesis).

Anyway if you put parenthesis around the coalesce's in your original query, that should solve the problem. Example:

     SELECT
        ((coalesce(cnt_2_9, 0)) +
              (coalesce(cnt_30_59, 0)) +
              (coalesce(cnt_60_210, 0))) AS TOTAL,
        CURRENT_TIMESTAMP
     FROM table_a;

The other possibility is to use zeroifnull which behaves more like a function and doesn't require parenthesis (nor does it require the 0 to be provided as an argument).

As in:

     SELECT
        (zeroifnull(cnt_2_9) +
              zeroifnull (cnt_30_59) +
              zeroifnull (cnt_60_210)) AS TOTAL,
        CURRENT_TIMESTAMP
     FROM table_a;

Zerofifnull, as its name implies, will return 0 if the parameter is null, otherwise it returns the supplied parameter.

Hope this helps

Glenn Mc

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