Dynamically construct SQL OPEN Query DB2 to Filter records in SQL Server Table

Question

Post reply

Dynamically construct SQL OPEN Query DB2 to Filter records in SQL Server Table

Welsh Corgi

SSC Guru

Points: 116520
More actions
December 1, 2011 at 7:11 am

#248928

I need to Dynamically construct SQL OPEN Query DB2 to Filter records in SQL Server Table.
I have tried various Joins, filtering, etc but no luck.
I tried the following an it ran forever and I ended up killing it:
SELECT *
FROMCompany AS co WITH (NOLOCK)
LEFT JOINOPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00' ) AS DB2
ON DB2.YP_Account_No = co.AccountNo

I even tried filtering it with a Date Range but no luck.
When I do the following I get a result within seconds:
SELECT *
FROMOPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00 WHERE YP_Account_No = 1014382' ) AS DB2
I need to change the following so that it dynamically constructs a SQL Statement and uses the AccountNos in the SQL Server Company Table for the IN Clause.
SELECT *
FROMOPENQUERY(LSCTLRDAT1, 'SELECT * FROM PMSPYP00 WHERE Account_No IN (7003303,7003050,7003055,7003013, 7002928,8062604,7003306,7002945,7003019, 7003006)' ) AS DB2
There are 134 Records in the SQL Server Company Table. Here is a sample:
Company.dbo.AccountNo
7003303
7003050
7003055
7003013
7002928
8062604
7003306
7002945
7003019
7003006
Any help would be greatly appreciated.:exclamationmark:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I'm going to do it in a cursor but a While Loop would be preferable.:w00t:

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

Create a work table in DB2 and insert all the Account Numbers, then select using "IN (SELECT AccountNumber FROM WorkTable)" in your OPENQUERY.

-- Gianluca Sartori

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

I almost have the query constructed however I have an issue where I need to assign the value )') AS DB2 to a variable so that I can concatenate with the rest of the SQL Statement.

The problem is with the ' Quote.

I'm trying to get the correct syntax.

Thanks.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

I posted my question concerning the Quote issue to the following URL since it is different than the initial question:

http://www.sqlservercentral.com/Forums/Forum392-1.aspx?Update=1

Thanks.:-)

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/