SQL statement ...

From: Sohail Shaikh <sohail.shaikh_at_starpower.net>
Date: Tue, 4 Apr 2000 11:32:49 -0400
Message-ID: <8cd1vl$dnd$1@bob.news.rcn.net>

Hi,

I am having trouble creating the following SQL statement. I am trying to retrieve all employee
records regardless of whether they have salary information or whether they have valid starting
date. First clause (salary record) works fine with (+) sign but it does not work with the subquery.
I was able to use ORed statement to make the query work. I am wondering if there is another
'nicer' way of doing the same thing.

SELECT Employee.szlastname, Employee.szfirstname, EmployeeSalary.dtstartdate FROM Employee, EmployeeSalary
WHERE Employee.ulemployeeid = EmployeeSalary.ulemployeeid (+)

    AND EmployeeSalary.dtstartdate = (SELECT MAX(dtstartdate)

                                                            FROM
EmployeeSalary
                                                            WHERE

ulemployeeid = Employee.ulemployeeid)(+)

SELECT Employee.szlastname, Employee.szfirstname, EmployeeSalary.dtstartdate FROM Employee, EmployeeSalary
WHERE Employee.ulemployeeid = EmployeeSalary.ulemployeeid (+)

    AND (EmployeeSalary.dtstartdate = (SELECT MAX(dtstartdate)

                                                            FROM
EmployeeSalary
                                                            WHERE
ulemployeeid = Employee.ulemployeeid) OR
               EmployeeSalary.dtstartdate IS NULL)

Thanks in advance.

Sohail Received on Tue Apr 04 2000 - 10:32:49 CDT