Re: nvl returns strange value

durnylka_at_gazeta.pl wrote:
> Hi all,
>
> Lat us assume that a table named a_table is empty.
>
> Is there anybody who knows why the script:
>
> set null 0
>
> column c57 new_value av_sum_c57
>
> select sum(nvl(t3101_expired,0)) c57
> from
> a_table
> /
> prompt TEST: &av_sum_c57
>
> returns the following value
>
> TEST: -~
>
> But after small correction:
>
> set null 0
>
> column c57 new_value av_sum_c57
>
> select nvl(sum(nvl(t3101_expired,0)),0) c57
> from
> a_table
> /
> prompt TEST: &av_sum_c57
>
> returns correctly:
>
> TEST: 0
>
>
> Is that a bug?
>
> I will appreciate your help.

This is working as expected.

Test case:
CREATE TABLE T1 (T3101_EXPIRED NUMBER(22)); No insert statement - no rows in the table: SELECT
  SUM(NVL(T3101_EXPIRED,0)) C57
FROM
  T1;

C57 is returned as NULL

SELECT
  NVL(SUM(T3101_EXPIRED),0) C57
FROM
  T1;

C57

   0

The above changes the NULL value to 0.

SELECT
  COUNT(NVL(T3101_EXPIRED,0)) C57
FROM
  T1;

C57

   0

Now, add a row with a NULL value:
INSERT INTO T1 VALUES (NULL); SELECT
  SUM(NVL(T3101_EXPIRED,0)) C57
FROM
  T1;

C57

   0

SELECT
  COUNT(NVL(T3101_EXPIRED,0)) C57
FROM
  T1;

C57

   1

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Tue Nov 14 2006 - 06:56:27 CST