Joins in 8.1.6 [message #20421] |
Wed, 22 May 2002 14:21 |
VENUGOPAL B TIRUMALA
Messages: 1 Registered: May 2002
|
Junior Member |
|
|
Hi we are using Oracle 8.1.6 and running this query aganist the table called 'names' and it is not working if there are future dated rows. The table has following rows,
Row #1 826480,PRI,GILLILAND,E,04/14/1997,USA,Gilliland,E Elaine, ,Gilliland,E,Elaine,
Row #2 826480,PRI,GILLILAND,E,05/24/2002,USA,Gilliland,E Elaine, ,Gilliland,E,Elaine,
The query is as follows and it won't give any rows
SELECT A.EMPLID
,A.NAME_TYPE
,A.EFFDT
FROM PS_NAMES A
WHERE A.NAME_TYPE = 'PRI'
AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B
WHERE B.EMPLID = A.EMPLID
AND B.NAME_TYPE = A.NAME_TYPE
AND ( B.EFFDT <= SYSDATE
OR (B.EFFDT > SYSDATE
AND SYSDATE < (SELECT MIN(C.EFFDT) FROM PS_NAMES C
WHERE C.EMPLID = B.EMPLID
AND C.NAME_TYPE = B.NAME_TYPE) ) ))
Please help me.
Thanks
Venu
|
|
|
|
Re: Joins in 8.1.6 [message #20456 is a reply to message #20421] |
Sat, 25 May 2002 09:48 |
Su
Messages: 154 Registered: April 2002
|
Senior Member |
|
|
Sure enough it does not work. Let us see how the analysis goes here. The culprit is in your subquery.
Check the following piece of (cut and paste of)your code.
( B.EFFDT <= SYSDATE
OR (B.EFFDT > SYSDATE
AND SYSDATE < (SELECT MIN(C.EFFDT) FROM PS_NAMES C
WHERE C.EMPLID = B.EMPLID
AND C.NAME_TYPE = B.NAME_TYPE) )
You are checking the effective date that is lessthan or equal to SYSDATE (B.EFFDT <= SYSDATE), OR effective date is greater than SYSDATE (a future effective date), but it should be prior to the MINimum effective date available in the databse for that employee. Since the database has all the dates including , prior to SYSDATE, current date and future dates, how can one condition with MINimum satisfies as a future date. It is misleading the ORACLE itself.
The condition
(B.EFFDT > SYSDATE
AND SYSDATE < (SELECT MIN(C.EFFDT) FROM PS_NAMES C
WHERE C.EMPLID = B.EMPLID
AND C.NAME_TYPE = B.NAME_TYPE) would never be true as the database has previous, current and future dates for an employee and the future date will never be lower than a minimum date, which possibly a past date, for that employee. The above piece of code does not return true. I guess, by mistake you have given the MIN() in place of MAX().
Check it out.
Good luck :)
|
|
|