| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Help with SQL & Aggregate Functions
You probably hit the following bug!!!
Doc ID:
Note:71232.1
Subject:
OERR: ORA-24347 Warning of a
NULL column in an aggregate
function
Bug:1149002 SQLPLUS treated
this warning as an ERROR
"Lisa R.
Clary" To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
<lisa_at_cog.ufl. cc:
edu> Subject: RE: Help with SQL & Aggregate Functions
Sent by:
root_at_fatcity.c
om
05/30/2002
01:57 PM
Please respond
to ORACLE-L
Here is the query re-write that works--basically just moved the group by to its own select. The only thing that would prevent the error message was to remove the count().
REWRITTEN code below that works--still don't have an answer for the
original
question.
select s.study_id, s.strat_code, s.short_name, nvl(o.total,0) TOTAL from (select count (*) total, study_id, stratum from on_study group by study_id, stratum) o,
regdba.reg_stratum s,
regdba.study y
where y.stat_center_code in (1,7) and
s.study_id = o.study_id(+) and
s.study_id = y.study_id and
s.strat_code = o.stratum (+)
-----Original Message-----
Clary
Sent: Thursday, May 30, 2002 1:24 PM
To: Multiple recipients of list ORACLE-L
I am having a hard time understanding why removing the line
"y.date_accrual_close is not null and" generates the error
"ERROR:ORA-24347:
Warning of a NULL column in an aggregate function" and returns 0 rows. I
can
re-write the query to get it to do what I need, but I don't understand why
this value has anything to do with the count/group by....so what if it is
null? there are other fields in the regdba.study table that are null..
I did RTFM, but saw nothing that applied---date_accrual_close is neither in the select nor in the group by.
Thanks for your input!
lc
select s.study_id, s.strat_code, s.short_name,
count(o.study_id)
from rdedba.on_study o,
regdba.reg_stratum s,
regdba.study y
where y.stat_center_code in (1,7) and
y.study_type_code <> 7 and
y.date_accrual_close is not null and
s.study_id = o.study_id(+) and
NVL(s.strat_code,0) = NVL(o.stratum(+),0) and
s.study_id = y.study_id
group by s.study_id, s.strat_code, s.short_name
order by s.study_id desc, s.strat_code;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa R. Clary
INET: lisa_at_cog.ufl.edu
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa R. Clary
INET: lisa_at_cog.ufl.edu
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: Rick_Cale_at_teamhealth.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Liststo: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Thu May 30 2002 - 13:38:35 CDT
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
 |
 |