Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tariq2
Posting Yak Master
125 Posts |
 Posted - 2011-04-12 : 09:25:59
|
| Hi,I am attempting to query transactions in F0911 table with a gldoc = 11029289 in symprod_etldev database. These need to be within the Poland territory in d_businessunits table in biprod database.I have attempted the following correlated sub-query but to no avail.Help is much appreciated.select * from openquery(symprod_etldev,'select * from proddta.f0911 where gldoc = ''11029289'' and glco in (select leid from biprod.dbo.D_BusinessUnits where TerritoryName = ''Poland'') ')I receive the following error message:OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev" returned message "ORA-00907: missing right parenthesis".Msg 7321, Level 16, State 2, Line 1An error occurred while preparing the query "select * from proddta.f0911 where gldoc = '11029289' and glco in (select leid from biprod.dbo.D_BusinessUnits where TerritoryName = 'Poland') " for execution against OLE DB provider "OraOLEDB.Oracle" for linked server "symprod_etldev". |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-04-13 : 07:52:39
|
| Where is biprod? I'm guessing it is on your source server so can't be referenced from Oracle.You might try creating a table on the oracle server and populating it with the values from biprod.dbo.D_BusinessUnits then joining to it or passing the values as part of the query - maybe looping for each one depending on how many there are.Or get the rows onto sql server then do the join there.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
correlated sub-query between two databases