When joining an OPENQUERY, you do not need to include the OPENQUERY in a sub query, try this:

select

[ApEmail]

,[FirstName]

,[LastName]

,[UEmail]

,[PEmail]

,[SCode]

,r.AY, r.JN, r.APID, r.APUNAME, r.EMAIL, r.FIRST_NAME, r.LAST_NAME, r.APSTATUS

from MUser inner join

OPENQUERY(abc, 'SELECT AY, JN, APID, APUNAME, EMAIL, FIRST_NAME, LAST_NAME, APSTATUS FROM abc.DATA') r

on ApEmail = r.Email or PEmail = r.Email or UEmail = r.Email

where SCode = 'ns'

As for the other people with the issue of the No Data error message for openquery used as a sub query, I believe that the sql server may be running the sub query for the first record, then the following records find no data when trying to link back to the records retrieve by the first record, to test, add Top 1 after select, as Select Top 1 ..., that should work, where as, Select Top 2 does not work and give the No Data error message.

Update:

For future reference I discovered in my own query with multiple tables and a subquery that selects from a linked server via openquery, that in the main query FROM clause (even though I'm using mostly inner joins) if I move the tables around and select from the most narrowing table first (eg. selecting from transactions table first when I'm only selecting transactions from one day), then the 'No Data' error message is resolved (ie. no more error).

Update 2:

Well I finally figured it out :w00t: I stumbled on the solution when I was refining a crystal report with a sql expression field to a linked server connection, which was working fine until I removed one of the tables from the primary query, crazy as this sounds removing that table made the primary query run faster and when it was time to retrieve the data from the linked server the 'No Data' error message was displayed, apparently if the linked server connection data is not ready by the time the primary query needs the data, then instead of waiting for the data it will assume there is no data and give the 'No Data' error message which causes the whole report to blow up. Adding that table back in (with an enforce clause in crystal) even though I was not selecting any data from that table, made the report run about twice as long and resolved the 'No Data' error message. This also helps to make sense out of an issue with the report when the server was upgraded, before the upgrade the report ran just fine, after the upgrade the report started giving the 'No Data' error message, apparently the report was running much faster on the new server and that caused the data from the linked server to not be ready in time. Hopefully this helps someone, it will certainly help me in the future. 😀