OraFAQ Forum: SQL & PL/SQL » Joins in 8.1.6

Home » SQL & PL/SQL » SQL & PL/SQL » Joins in 8.1.6

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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

Report message to a moderator

Re: Joins in 8.1.6 [message #20422 is a reply to message #20421]

Wed, 22 May 2002 14:43

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

thats wierd,
i just ran your very same query. seeems to be working for me.

SQL> select * from ps_names;

EMPLID NAMETYPE EFFDT
---------- ---------- ---------
826480 PRI 14-APR-97
826480 PRI 24-MAY-02

SQL> get qe
1 SELECT A.EMPLID
2 ,A.NAMETYPE
3 ,A.EFFDT
4 FROM PS_NAMES A
5 WHERE A.NAMETYPE = 'PRI'
6 AND A.EFFDT = ( SELECT MAX(B.EFFDT) FROM PS_NAMES B
7 WHERE B.EMPLID = A.EMPLID
8 AND B.NAMETYPE = A.NAMETYPE
9 AND ( B.EFFDT <= SYSDATE
10 OR (B.EFFDT > SYSDATE
11 AND SYSDATE < (SELECT MIN(C.EFFDT) FROM PS_NAMES C
12 WHERE C.EMPLID = B.EMPLID
13* AND C.NAMETYPE = B.NAMETYPE) ) ))
SQL> /

EMPLID NAMETYPE EFFDT
---------- ---------- ---------
826480 PRI 14-APR-97

SQL> alter session set nls_date_format='mm/dd/yyyy';

Session altered.

SQL> select * from ps_names;

EMPLID NAMETYPE EFFDT
---------- ---------- ----------
826480 PRI 04/14/1997
826480 PRI 05/24/2002

SQL> @qe

EMPLID NAMETYPE EFFDT
---------- ---------- ----------
826480 PRI 04/14/1997

SQL>

Report message to a moderator

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

Report message to a moderator

Previous Topic:	Subquery
Next Topic:	SQL coding question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 10:49:34 CDT 2024