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/

• Welsh Corgi

  SSC Guru

  Points: 116520

  More actions

  December 1, 2011 at 7:22 am

  #1415244

  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

  December 1, 2011 at 7:55 am

  #1415282

  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

  December 1, 2011 at 8:24 am

  #1415307

  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

  December 1, 2011 at 8:29 am

  #1415309

  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/