Avatar of sam2929
sam2929 asked on

Warning of a NULL column in an aggregate function.

I getting below warning  i tried NVl still getting error any ideas.

Nvl(Max(C.Pay_Rate),'0') As Pay_Rate_Max

ORA-24347: Warning of a NULL column in an aggregate function.

Oracle Database

Avatar of undefined
Last Comment
Sean Stuber

8/22/2022 - Mon
slightwv (䄆 Netminder)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Sean Stuber

reversing it doesn't make sense.

MAX should already exclude nulls,
  forcing a 0 into the middle is at best a wasted effort, at worst, data corruption
Sean Stuber

what version and in what context are you getting the error?

i am on 11g looks like Max(NVL(C.Pay_Rate,0)) As Pay_Rate_Max  is working but yes
there is data corruption  

 example i did

Min(NVL(C.Pay_Rate,0))  got 0
Max(NVL(C.Pay_Rate,0))  got 1006

without NVL
MIN(pay_rate) got 1006
max(pay_rate) got 1006
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
slightwv (䄆 Netminder)

Can you post more about what you are doing to generate the ORA-24347?

I'm not able to reproduce it with any test case I've set up.

It should be noted that this is a "warning" and not an error.

all that warning is telling you is that some values encountered in the table(s) hold a NULL and that the aggregate function is discarding those while performing the aggregation.

memory fails me with respect to Ora here, but in SQL Server you can turn off those warnings

approx: "sent ansi warnings off"

You could try the following to ascertain if my memory is way off:

select max(C.Pay_Rate) as max_pay_rate
from yourtable c
where C.Pay_Rate IS NOT NULL

-- expect: no ORA-24347 warnings

The question here is again the context in which the message is received.  As noted, it is a warning, so your query should be getting results.  The tool you are using is showing the message.  If it is custom code, then you probably don't want to display these types of warnings.

Based on the message text, it would seem to me that it is an OCI level message, I don't think that SQL*Plus would even show those.  The closest thing that I can find (or think of) in SQL*Plus is that you can have it exit on a warning.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Sean Stuber

The is an old bug that sql*plus would report the oci warnings as errors.

The database might be 11, what about the client?

Is sql*plus even involved here?  are you writing oci code?

yes its oci code i think i got work around
Sean Stuber

in oci, you simply capture that condition and ignore - unless you aren't expecting nulls, in which case an error is appropriate.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck