Solved

OPENQUERY with subquery use in where clause

Posted on 2016-09-06
2
20 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article I will describe the Detach & Attach 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.
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
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.

803 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