Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 175
  • Last Modified:

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
0
Narendra Kumar
Asked:
Narendra Kumar
  • 2
1 Solution
 
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

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now