Re: SQL statement ...

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: Fri, 07 Apr 2000 19:01:03 GMT
Message-ID: <8clb96$920$1@nnrp1.deja.com>

Try this:

SELECT
  Employee.szlastname
, Employee.szfirstname
, EmployeeSalary.dtstartdate
FROM
  Employee
, (

	SELECT
	  ulemployeeid
	, MAX(dtstartdate) dtstartdate
	FROM
	  EmployeeSalary
	GROUP BY
	  ulemployeeid

  ) EmployeeSalary
WHERE Employee.ulemployeeid = EmployeeSalary.ulemployeeid (+);

Michael J. Ort

In article <8cd1vl$dnd$1_at_bob.news.rcn.net>,   "Sohail Shaikh" <sohail.shaikh_at_starpower.net> wrote:
> 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
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Apr 07 2000 - 14:01:03 CDT