我目前正在使用链接服务器在SQL服务器上创建存储过程,"OPENQUERY";语句和临时表。我的目标是让一个源被多个第三方源使用,这样每个人都可以查看相同的数据。
我遇到的问题是,有些实例需要一个特定的Where子句,而其他实例不需要这个Where子句。有没有一种方法可以声明这个where子句等于某个东西,如果where子句为空,则使其无效,但如果填充了where子句,则使用where子句?我试着使参数等于"%"&"%?%&";,等等,但似乎什么都不起作用。
我还想指出,这是一个Oracle数据库,我正在从Microsoft SQL Server上提取它。我的代码在下面,如果留空,参数@WINS
是我试图取消的:
DECLARE @query_start DATETIME;
DECLARE @query_end DATETIME;
DECLARE @query_wins NVARCHAR(MAX);
SET @query_start = '7/1/2020';
SET @query_end = '7/15/2020';
SET @query_wins = 'F6666';
DECLARE @START_DATE NVARCHAR(MAX) = CONVERT(VARCHAR,@query_start,105)
DECLARE @END_DATE NVARCHAR(MAX) = CONVERT(VARCHAR,@query_end,105)
DECLARE @WINS NVARCHAR(MAX) = @query_wins
DECLARE @SqlCommand NVARCHAR(MAX) =
'
SELECT
*
FROM
OPENQUERY
(
PDB,
'' SELECT
T1.WELL_NUM
, D2.WELL_NAME
, T1.DAILY_RDG_DATE
, T1.GROSS_OIL_BBLS
, T1.GROSS_GAS_MCF
, T1.GROSS_WTR_BBLS
, T1.TUBING_PRESS
, T1.CASING_PRESS
, T1.GAS_LINE_PRESS
, T1.CHOKE,T1.CHOKE_SIZE AS CHOKE2
, T2.GAS_PROD_FORECAST
, T2.OIL_PROD_FORECAST
, T2.WTR_PROD_FORECAST
FROM
(PDB.T003031 T1
INNER JOIN WINS.DW_ANORM_ROWL@WINP_DBLINK.WORLD D2
ON T1.WELL_NUM = D2.WINS_NO
AND T1.CMPL_NUM = D2.CMPL_NO)
LEFT JOIN PDB.T000057 T2 ON T1.WELL_NUM = T2.WELL_NUM
AND T1.CMPL_NUM = T2.CMPL_NUM
AND T2.FORECAST_DATE=T1.DAILY_RDG_DATE
WHERE
D2.HOLE_DIRECTION = ''''HORIZONTAL''''
AND D2.ASSET_GROUP = ''''Powder River Basin''''
AND T1.DAILY_RDG_DATE > TO_DATE(''''' + CONVERT(VARCHAR,@START_DATE,105) + ''''',''''DD-MM-YYYY'''') - 2
AND T1.DAILY_RDG_DATE < TO_DATE(''''' + CONVERT(VARCHAR,@END_DATE,105) + ''''',''''DD-MM-YYYY'''')
AND D2.OPER_NON_OPER = ''''OPERATED''''
AND T1.WELL_NUM = ''''' + @WINS + '''''
''
)
'
PRINT @SqlCommand
DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (
WELL_NUM NVARCHAR(MAX)
, WELL_NAME NVARCHAR(MAX)
, DAILY_RDG_DATE DATETIME
, GROSS_OIL_BBLS FLOAT
, GROSS_GAS_MCF FLOAT
, GROSS_WTR_BBLS FLOAT
, TUBING_PRESS FLOAT
, CASING_PRESS FLOAT
, GAS_LINE_PRESS FLOAT
, CHOKE1 FLOAT
, CHOKE2 FLOAT
, GAS_PROD_FORECAST FLOAT
, OIL_PROD_FORECAST FLOAT
, WTR_PROD_FORECAST FLOAT
)
PRINT @SqlCommand
INSERT INTO #temp
EXEC sp_ExecuteSQL @SqlCommand
SELECT
WELL_NUM
, WELL_NAME
, DAILY_RDG_DATE
, ISNULL(GROSS_OIL_BBLS,0) AS 'GROSS_OIL_BBLS'
, ISNULL(GROSS_GAS_MCF,0) AS 'GROSS_GAS_MCF'
, ISNULL(GROSS_WTR_BBLS,0) AS 'GROSS_WTR_BBLS'
, ISNULL(TUBING_PRESS,0) AS 'TUBING_PRESS'
, ISNULL(CASING_PRESS,0) AS 'CASING_PRESS'
, ISNULL(GAS_LINE_PRESS,0) AS 'GAS_LINE_PRESS'
, ISNULL(CHOKE1,0) AS 'CHOKE1'
, ISNULL(CHOKE2,0) AS 'CHOKE2'
, ISNULL(GAS_PROD_FORECAST,0) AS 'CHOKE2'
, ISNULL(OIL_PROD_FORECAST,0) AS 'OIL_PROD_FORECAST'
, ISNULL(WTR_PROD_FORECAST,0) AS 'WTR_PROD_FORECAST'
FROM #temp
ORDER BY
DAILY_RDG_DATE ASC
DROP TABLE IF EXISTS #temp
您可以在SQL Server 中设置这样的可选参数
WHERE ISNULL(@parameter,column_name) = column_name
好的,如果我正确地抽象了您的问题,您将尝试根据字段的值应用不同的过滤逻辑。
您可以考虑为此使用游标,在表上进行迭代,并根据需要应用if/else。
您真的需要将SQL命令组合为字符串吗?也许你这么做只是为了在这里共享代码,希望如此?
不管怎样,听起来这对你有用:
WHERE (T1.WELL_NUM = @WINS OR @WINS IS NULL) AND
...other conditions...