RE: Outer join sql help?

From: Mercadante, Thomas F <NDATFM_at_labor.state.ny.us>
Date: Wed, 12 Dec 2001 10:35:19 -0800
Message-ID: <F001.003DB062.20011212101029@fatcity.com>

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: stvjston_at_sapphire.jcn1.com

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: 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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Mercadante, Thomas F
INET: NDATFM_at_labor.state.ny.us

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------