February 21, 2008 at 7:44 am
I've been struggling with this subselect in a OpenQuery. Help Please. All quotes are single quotes.
Insert Into dbo.PLExchRate
Select CCNVFC
From OpenQuery(DRAS400,'Select CCNVFC From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN''
And CCRTYP= ''MTHEND''
And CCDATE = (Select CCDATE From OpenQuery(DRAS400,''Select Max(CCDATE) As CCDATE From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN''''))')
I get the following error:
OLE DB provider "MSDASQL" for linked server "DRAS400" returned message "[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid. Valid tokens: ) UNION EXCEPT.".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "Select CCNVFC From BPCSFFG.GCC
Where CCFRCR = 'USD'
And CCTOCR = 'PLN'
And CCRTYP = 'MTHEND'
And CCDATE = (Select CCDATE
From OpenQuery(DRAS400,'Select Max(CCDATE) As CCDATE From BPCSFFG.GCC
Where CCFRCR = 'USD'
And CCTOCR = 'PLN''))" for execution against OLE DB provider "MSDASQL" for linked server "DRAS400".
February 21, 2008 at 8:17 am
you have an embedded OpenQuery() that's causing the problem. eliminate it and it should be fine.
'Select CCNVFC From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN''
And CCRTYP = ''MTHEND''
And CCDATE =
(Select Max(CCDATE) As CCDATE From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN'''')'
February 21, 2008 at 8:31 am
Thanks, Antonio. That was it! I ended up using this syntax:
Insert Into dbo.PLExchRate
Select CCNVFC
From OpenQuery(DRAS400,'Select CCNVFC From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN''
And CCRTYP = ''MTHEND''
And CCDATE = (Select Max(CCDATE) From BPCSFFG.GCC
Where CCFRCR = ''USD''
And CCTOCR = ''PLN'')')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply