SELECT * INTO #C_INFO FROM OPENQUERY(ORACLE_CMIDW1,' SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between
''2012-03-01'' And ''2013-10-31'' AND Not A.ISSUE = 110 AND A.TYPE_ID = 1
and B.ENT_ID In (2,3) ')
Above sql works perfect but i dont want to hard code the dates. That's the reason trying to use variable. date format (yyyy-mm-dd, '2013-03-01')
, anything other than this date format, Oracle doesn't execute the sql.
上面的SQL工作完美,但我不想硬编码日期。这就是尝试使用变量的原因。日期格式(yyyy-mm-dd,'2013-03-01'),除此日期格式以外的任何内容,Oracle都不执行sql。
Is it possible to do this, If so please re write the whole sql using variable so that i can see how it can be done.
是否可以这样做,如果是这样,请使用变量重写整个sql,以便我可以看到它是如何完成的。
Thanks!
2 个解决方案
#1
8
The below code should do the trick. You can't pass a variable to an OPENQUERY. However, you can dynamically generate the code that you will execute via OPENQUERY.
下面的代码应该可以解决问题。您无法将变量传递给OPENQUERY。但是,您可以动态生成将通过OPENQUERY执行的代码。
DECLARE @StartDate DATETIME = '2012-03-01';
DECLARE @EndDate DATETIME = '2013-10-31';
DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT *
INTO #C_INFO
FROM OPENQUERY(ORACLE_CMIDW1,
''SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID
FROM XXX B
INNER JOIN YYY A
ON B.ANO = A.ANO
WHERE A.REVIEW_COMP_DATE BETWEEN ''''' + CONVERT(CHAR(10), @StartDate, 120) + ''''' AND ''''' + CONVERT(CHAR(10), @EndDate, 120) + '''''
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 and B.ENT_ID In (2,3)'');';
PRINT @SqlCommand
EXEC sp_ExecuteSQL @SqlCommand;
On a separate note, I would recommend looking into replacing your linked server query with a SSIS package that loads the data. Although the above code works, this is not the most efficient way to transfer data from an Oracle server to SQL Server.
另外,我建议您考虑使用加载数据的SSIS包替换链接服务器查询。虽然上面的代码有效,但这不是将数据从Oracle服务器传输到SQL Server的最有效方法。
#2
0
This should do it:
这应该这样做:
DECLARE @startDate DATETIME = '2013-06-01'
DECLARE @endDate DATETIME = '2013-07-01'
INSERT INTO #C_INFO
EXECUTE('SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between ? And ?
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 AND B.ENT_ID In (2,3) ', @startDate , @endDate) AT ORACLE_CMIDW1
#1
8
The below code should do the trick. You can't pass a variable to an OPENQUERY. However, you can dynamically generate the code that you will execute via OPENQUERY.
下面的代码应该可以解决问题。您无法将变量传递给OPENQUERY。但是,您可以动态生成将通过OPENQUERY执行的代码。
DECLARE @StartDate DATETIME = '2012-03-01';
DECLARE @EndDate DATETIME = '2013-10-31';
DECLARE @SqlCommand NVARCHAR(MAX) = N'SELECT *
INTO #C_INFO
FROM OPENQUERY(ORACLE_CMIDW1,
''SELECT A.CID, A.ANO, A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID
FROM XXX B
INNER JOIN YYY A
ON B.ANO = A.ANO
WHERE A.REVIEW_COMP_DATE BETWEEN ''''' + CONVERT(CHAR(10), @StartDate, 120) + ''''' AND ''''' + CONVERT(CHAR(10), @EndDate, 120) + '''''
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 and B.ENT_ID In (2,3)'');';
PRINT @SqlCommand
EXEC sp_ExecuteSQL @SqlCommand;
On a separate note, I would recommend looking into replacing your linked server query with a SSIS package that loads the data. Although the above code works, this is not the most efficient way to transfer data from an Oracle server to SQL Server.
另外,我建议您考虑使用加载数据的SSIS包替换链接服务器查询。虽然上面的代码有效,但这不是将数据从Oracle服务器传输到SQL Server的最有效方法。
#2
0
This should do it:
这应该这样做:
DECLARE @startDate DATETIME = '2013-06-01'
DECLARE @endDate DATETIME = '2013-07-01'
INSERT INTO #C_INFO
EXECUTE('SELECT A.CID, A.ANO,
A.COMP_REVD_DATE, A.REVIEW_COMP_DATE, A.ISSUE, B.ENT_ID, A.TYPE_ID FROM XXX
B INNER JOIN YYY A ON B.ANO = A.ANO WHERE A.REVIEW_COMP_DATE Between ? And ?
AND Not A.ISSUE = 110 AND A.TYPE_ID = 1 AND B.ENT_ID In (2,3) ', @startDate , @endDate) AT ORACLE_CMIDW1