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
<[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
edu> Subject: RE: Help with SQL & Aggregate
Functions
Sent by:
[EMAIL PROTECTED]
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 (+)
order by s.study_id desc, s.strat_code;
-----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: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Lisa R. Clary
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
