0

I am facing below error with where clause after I am using AND condition in join:

OLE DB provider "OraOLEDB.Oracle" for linked server "xyz" returned message "ORA-01403: no data found". Msg 7346, Level 16, State 2, Line 1 Cannot get the data of the row from the OLE DB provider "OraOLEDB.Oracle" for linked server "xyz".

Below query is working fine :

SELECT DISTINCT a.FileNumber, a.FileSub, a.CurrentBatchRecordID, b.BatchName, b.BatchID,b.BatchStatusID,
 b.CreateDate, b.IOGCBatch,e.Area,e.LandDistrict,f.province,f.name
 FROM Lease.tFileSubReviewers AS a
  LEFT OUTER JOIN Lease.tBatchHeader AS b ON b.RecordID = a.CurrentBatchRecordID
  LEFT OUTER JOIN Admin.tUser AS c 
  ON c.RecordID = a.ReviewerID    
 LEFT OUTER JOIN Lease.tFileSubDetail as e on a.FileNumber=e.FileNumber 
    LEFT OUTER join  openquery("xyz",
 'SELECT Code ,RTRIM(PROVINCE) AS PROVINCE,RTRIM(NAME) AS NAME FROM CSL_T_AREA order by province') as f 
 on f.code=e.Area where a.ReviewerID=179

But if I add an AND condition to join prior to the openquery join as below:

 SELECT DISTINCT a.FileNumber, a.FileSub, a.CurrentBatchRecordID, b.BatchName, b.BatchID,b.BatchStatusID,
 b.CreateDate, b.IOGCBatch,e.Area,e.LandDistrict,f.province,f.name
 FROM Lease.tFileSubReviewers AS a
  LEFT OUTER JOIN Lease.tBatchHeader AS b ON b.RecordID = a.CurrentBatchRecordID
  LEFT OUTER JOIN Admin.tUser AS c 
  ON c.RecordID = a.ReviewerID 

 LEFT OUTER JOIN Lease.tFileSubDetail as e on a.FileNumber=e.FileNumber **and a.FileSub =e.FileSub**  
  LEFT OUTER join  openquery("xyz",
 'SELECT Code ,RTRIM(PROVINCE) AS PROVINCE,RTRIM(NAME) AS NAME FROM CSL_T_AREA order by province') as f 
 on f.code=e.Area where a.ReviewerID=179

I get an error as shown at the top. Even if i do not include where condition at the end in 2nd query, query runs fine.

Can anyone help me with this?

Thanks.

Lakshay Anand
  • 1
  • 1
  • Did you mean to have the 'enter code here' before the where clause in your code which is erroring? – DarkMark Jan 12 '18 at 15:26
  • Seems to be an issue with certain optimizer decisions when no data is available, and adding the extra join predicate changes the optimizer's decision enough to avoid the error. Pretty wonky if so. https://stackoverflow.com/questions/19225551/sql-server-linked-server-to-oracle-returns-no-data-found-when-data-exists – Aaron Dietz Jan 12 '18 at 15:37

0 Answers0