Link to home
Start Free TrialLog in
Avatar of OceanReefClub
OceanReefClubFlag for United States of America

asked on

Querying a linked server....

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.  

Any ideas????

Thanks for your help!
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

select * from openquery(timesaver, 'select * from employee
inner join (
    select employeeId, effectiveDate, status
    from employeepay pay1
    inner join (
        select max(effectivedate) AS effectiveDate, employeeid
        from employeepay
        group by employeeid
    ) lastStatus ON lastStatus.employeeid = pay1.employeeid AND lastStatus.effectiveDate = pay1.effectiveDate
) pay2 ON employee.employeeid = pay2.employeeid
')
Avatar of OceanReefClub

ASKER

I get the following error when executing:
Server: Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing a query for execution against OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: [Sybase][ODBC Driver][Adaptive Server Anywhere]Syntax error or access violation: Column 'employeeId' found in more than one table -- need a correlation name]
OLE DB error trace [OLE/DB Provider 'MSDASQL' ICommandPrepare::Prepare returned 0x80040e14].

ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
That did it...
Thanks so much for your help!!
Np, glad it worked!  [And sorry about the first one -- the parser is very exacting when working on linked servers.]