Solved

OPENQUERY with subquery use in where clause

Posted on 2016-09-06
2
14 Views
Last Modified: 2016-09-26
Here I am using OLEDB as link server and accessing OLEDB table by select statement.
I am inserting OLEDB table data into MS SQL 2008 R2 Data base table .
When I passed the static time as parameter in select statement of OPENQUERY it is working fine .


INSERT INTO [TEST].[dbo].[TestCount]
           ([tag]
           ,[time]
           ,[value]            
           )    
    SELECT  * FROM OPENQUERY([TEST_DEV],
      '
      SELECT [tag], [time], [value]
      FROM [piarchive]..[picomp2]
      WHERE [tag] = ''TEST_MC_3_NumberOfUninspectedProducts''
      and [value] IS NOT NULL
      and [time] > ''2016-08-17 12:57:42''
      order by [time] asc      
      ')
      
GO

--***********************************************
 when try to pass time parameter value as subquery in OPENQUERY select statement it is not taking that value.
How can i pass subquery in OPENQUERY select statement ?

INSERT INTO [TEST].[dbo].[TestCount]
           ([tag]
           ,[time]
           ,[value]            
           )    
    SELECT  * FROM OPENQUERY([TEST_DEV],
      '
      SELECT [tag], [time], [value]
      FROM [piarchive]..[picomp2]
      WHERE [tag] = ''TEST_MC_3_NumberOfUninspectedProducts''
      and [value] IS NOT NULL
      and [time] > ''select MAX(time) from   [TEST].[dbo].[TestCount]''
      order by [time] asc      
      ')      
GO
0
Comment
Question by:Narendra Kumar
  • 2
2 Comments
 
LVL 40

Accepted Solution

by:
Sharath earned 500 total points (awarded by participants)
ID: 41787430
INSERT INTO [TEST].[dbo].[TestCount]
           ([tag]
           ,[time]
           ,[value]            
           )    
    SELECT  * FROM OPENQUERY([TEST_DEV],
      '
      SELECT [tag], [time], [value]
      FROM [piarchive]..[picomp2]
      WHERE [tag] = ''TEST_MC_3_NumberOfUninspectedProducts''
      and [value] IS NOT NULL
      and [time] > (select MAX(time) from   [TEST].[dbo].[TestCount])
      order by [time] asc      
      ')      
GO

Open in new window

0
 
LVL 40

Expert Comment

by:Sharath
ID: 41815725
Corrected the syntactical errors in the code.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now