Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

OPENQUERY with subquery use in where clause

Posted on 2016-09-06
2
Medium Priority
?
104 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
2 Comments
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 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 41

Expert Comment

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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

618 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