Re: 'SELECT MAX' subquery problem

When using a outerjoin, then all referenced fields in the where-clause has to have (+). I think the t.statusdate will give you now no rows and perhaps the t.contractstatus aswell.

greetings, Rob

"Andyza" <andyza_at_webmail.co.za> schreef in bericht news:1108637812.430640.153190_at_g14g2000cwa.googlegroups.com...
> My Oracle 9i db table structure is:
>
> Members (sysid(PK), classid, personalid, firstname, surname,
> dateofbirth, deleted)
>
> Education (qualificationid(PK), personalid(FK), qualificationtype,
> qualificationtitle, yearobtained)
>
> Terminations (terminationid(PK), sysid(FK), contractstatus, statusdate)
>
> I'm trying to select all the members who are assigned to a particular
> class (12345) and who have not been removed from that class (I keep a
> record of students that are removed from each class). The query below
> returns 0 results when there should be at least 3 (I set up the test
> data so that there would be a number of 'removed' and 'active'
> students).
>
> SELECT m.sysid, m.classid, m.personalid, m.firstname, m.surname,
> m.dateofbirth, e.qualificationtype, e.qualificationtitle,
> e.yearobtained, t.contractstatus,
> TO_CHAR (t.statusdate, 'YYYY/MM/DD') AS statusdate
> FROM members m,
> education e,
> terminations t
> WHERE (m.deleted = 'No')
> AND ( (m.personalid = e.personalid(+))
> AND (m.sysid = t.sysid(+))
> AND (t.contractstatus <> 'Removed')
> AND (m.classid = '12345')
> AND (t.statusdate = (SELECT MAX (t.statusdate)
> FROM terminations t
> WHERE t.sysid = m.sysid))
> )
> GROUP BY m.sysid, m.classid, m.personalid, m.firstname, m.surname,
> m.dateofbirth, e.qualificationtype,
> e.qualificationtitle, e.yearobtained,
> t.contractstatus, t.statusdate
> ORDER BY LOWER(m.surname) ASC, LOWER(m.firstname) ASC
>
> The 'terminations' table does not contain any records for a student
> until he/she is 'removed' from the class or 'reassigned' to the class.
> All students are initially automatically assigned to the class and are
> members of just this one class.
>
> I think the problem is with the 'SELECT MAX' subquery, but what?
>
Received on Thu Feb 17 2005 - 14:57:24 CST