Link to home
Create AccountLog in
Avatar of sam2929
sam2929

asked on

Warning of a NULL column in an aggregate function.

Hi,
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.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Sean Stuber
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
what version and in what context are you getting the error?
Avatar of sam2929

ASKER

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
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.
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?
Avatar of sam2929

ASKER

yes its oci code i think i got work around
in oci, you simply capture that condition and ignore - unless you aren't expecting nulls, in which case an error is appropriate.