'SELECT MAX' subquery problem

Andyza · *nix forums beginner Joined: 17 Feb 2005 Posts: 5

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?

Rob Abrahams · *nix forums beginner Joined: 12 Feb 2005 Posts: 2

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@webmail.co.za> schreef in bericht
news:1108637812.430640.153190@g14g2000cwa.googlegroups.com...

rogergorden@gmail.com · *nix forums beginner Joined: 21 Jun 2005 Posts: 14

Just a thought:

Oracle9i supports ANSI standard join so feel free to use LEFT OUTER or
RIGHT OUTER or FULL in your query syntax.

Roger Gorden
Senior DBA
Viewpoint Corp

Google