Outer join sql help?
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='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
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).
RE: Outer join sql help?
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/')
And TO_DATE('12/31/2001 235959','MM/DD/ 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='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
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).
RE: Outer join sql help?
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='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
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 l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
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).
RE: Outer join sql help?
I want to show all the available leave types in table a even if there are no records
in table l
-- Original Message --
Reply-To: [EMAIL PROTECTED]
Date: Wed, 12 Dec 2001 10:15:33 -0800
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='000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
This should work if you are looking for LEAVE_DETL_TBL records without
records in EFF_LEVPOL_EVNT_TYPE, which does not sound right.
I'm thinking you want the a records even when there is no supporting l
records:
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 l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
AND l.EFFECTIVE_DT Between TO_DATE('01/01/2001','MM/DD/')
And TO_DATE('12/31/2001','MM/DD/')
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
But then the date range gives you a problem, so:
SELECT a.LEAVE_CATEG_ID,
a.LEAVE_TYPE_CD,
a.LPET_LONG_DD,
Sum(decode(sign(l.EFFECTIVE_DT -
TO_DATE('01/01/2001','MM/DD/'),
1,0,NULL,0,
decode(l.EFFECTIVE_DT -
TO_DATE('12/31/2001','MM/DD/'),
1, 0 , l.ORIGINAL_INPUT_AM
) ) )
FROM EFF_LEVPOL_EVNT_TYPE_TBL a, LEAVE_DETL_TBL l
WHERE l.LEAVE_TYPE_CD(+) = a.evnt_type_cd
AND l.INTERNAL_EMPL_ID(+) = '000357'
GROUP BY a.LEAVE_CATEG_ID, a.LEAVE_TYPE_CD, a.LPET_LONG_DD;
Nasty - or is my initial guess off base?
Brian Norrell
Manager, MPI Development
QuadraMed
511 E John Carpenter Frwy, Su 500
Irving, TX 75062
(972) 831-6600
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Norrell, Brian
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: 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).
