Narendra Kumar
asked on
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_NumberOfUninsp ectedProdu cts''
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_NumberOfUninsp ectedProdu cts''
and [value] IS NOT NULL
and [time] > ''select MAX(time) from [TEST].[dbo].[TestCount]''
order by [time] asc
')
GO
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_NumberOfUninsp
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_NumberOfUninsp
and [value] IS NOT NULL
and [time] > ''select MAX(time) from [TEST].[dbo].[TestCount]''
order by [time] asc
')
GO
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Corrected the syntactical errors in the code.