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
sam2929Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can't you just reverse it (and remove the single quotes)?

Max(NVL(C.Pay_Rate,0)) As Pay_Rate_Max
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sdstuberCommented:
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
0
sdstuberCommented:
what version and in what context are you getting the error?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

sam2929Author Commented:
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
0
slightwv (䄆 Netminder) Commented:
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.
0
PortletPaulfreelancerCommented:
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
0
johnsoneSenior Oracle DBACommented:
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.
0
sdstuberCommented:
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?
0
sam2929Author Commented:
yes its oci code i think i got work around
0
sdstuberCommented:
in oci, you simply capture that condition and ignore - unless you aren't expecting nulls, in which case an error is appropriate.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.