Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1005
  • Last Modified:

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!
0
OceanReefClub
Asked:
OceanReefClub
  • 3
  • 2
1 Solution
 
Scott PletcherSenior DBACommented:
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
')
0
 
OceanReefClubAuthor Commented:
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].

0
 
Scott PletcherSenior DBACommented:
select * from openquery(timesaver, '
select employee.*, pay2.effectiveDate, pay2.status
from employee
inner join (
    select pay1.employeeId, pay1.effectiveDate, pay1.status
    from employeepay pay1
    inner join (
        select max(pay0.effectivedate) AS effectiveDate, pay0.employeeid
        from employeepay pay0
        group by pay0.employeeid
    ) lastStatus ON lastStatus.employeeid = pay1.employeeid AND lastStatus.effectiveDate = pay1.effectiveDate
) pay2 ON employee.employeeid = pay2.employeeid
')
0
 
OceanReefClubAuthor Commented:
That did it...
Thanks so much for your help!!
0
 
Scott PletcherSenior DBACommented:
Np, glad it worked!  [And sorry about the first one -- the parser is very exacting when working on linked servers.]
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now