Steve,
Generally, Sum and Group by functions do not prevent data from being
returned.
I'm not sure what you mean by not giving you the rows you expect. I would
look closely at the where clause to be sure you are selecting all the
records you want to get.
Select the rows without the group by to see what records seem to be missing.
Look especially closely at the date columns. Your BETWEEN clause only
selects dates with time stamps between 1/1/2001 00:00:00 and 12/31/2001
00:00:00 - note that records with dates of 12/31 will not be selected if
they have a time stamp > 0. You might try changing the between clause to:
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/YYYY')
And TO_DATE('12/31/2001 235959','MM/DD/YYYY hh24miss')
Hope this helps
Tom Mercadante
Oracle Certified Professional
-----Original Message-----
Sent: Wednesday, December 12, 2001 12:42 PM
To: Multiple recipients of list ORACLE-L
This sql doesn't give me the additional rows I'm expecting.. Is this because
of the sum and group by expressions? Is there a way around this behaviour?
Oracle 8.1.6
SELECT
a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(l.ORIGINAL_INPUT_AM)
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE a.LEAVE_TYPE_CD(+) = l.evnt_type_cd AND
l.INTERNAL_EMPL_ID='0000000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/YYYY')
And TO_DATE('12/31/2001','MM/DD/YYYY')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Johnston, Steve
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: Mercadante, Thomas F
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).
