OPENQUERY with subquery use in where clause

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
Narendra KumarAsked:
Who is Participating?
 
SharathData EngineerCommented:
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
 
SharathData EngineerCommented:
Corrected the syntactical errors in the code.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.