Querying a linked server....
Posted on 2006-06-01
I have a linked SQL Anywhere server, TIMESAVER, with two tables I need to join to get information from.
In the EMPLOYEES table, there is a record for every employee, with the EMPLOYEEID being the primary key.
In the EMPLOYEESPAY table, there is a record for every employee ID, and a listing of the status of that employee (Inactive, Deceased, Terminated, Full-Time Hourly, Part-time Hourly, etc.) and all changes of that status differentiated by the LASTUPDATED date/time field. I'm trying a list that includes every employee, and the current status of that employee.
When querying the linked server, I am forced to use OPENQUERY, or I get a catalog/schema error.
"Invalid schema or catalog specified for provider 'MSDASQL'.
OLE DB error trace [Non-interface error: Invalid schema or catalog specified for the provider.]."
I came up with the following query which gets me close, but not close enough:
select * from openquery(timesaver, 'select * from employee') a
inner join openquery(timesaver, 'select max(effectivedate), employeeid, statuscode from employeepay group by employeeid, statuscode') b
on a.employeeid = b.employeeid
Unfortunately, this will give me multiple records for those employees who had a change in statuscode, due to the group by in the query. Further, I've tried to put the seperate queries into views, and this failed as there was no begin transaction qualification. Am I missing something simple? I need the latest date (max(effectivedate), and the statuscode applicable to that date joined with the employee information related to that record.
Thanks for your help!