Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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