OPEN QUERY中的WHERE子句 [英] WHERE clause in OPEN QUERY

问题描述

我已经在SQL Server 2012中创建了一个过程，目的是从Oracle表中获取昨天的数据并将其插入到SQL Server表中 像这样使用OPENQUERY:

I have created a procedure in SQL Server 2012 for the purpose of fetching the yesterday data from an Oracle table and inserting it into a SQL Server table using OPENQUERY like this:

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL
     DROP TABLE #Temp 

SELECT   
    LEAD(CONVERT(VARCHAR, CONVERT(DATETIME, '01-JAN-1970 03:00:00', 120) + [DAT_CLOSEDATE] / (24 * 60 * 60), 120), 1, CONVERT(VARCHAR, CONVERT(DATETIME, '01-JAN-1970 03:00:00', 120) + [DAT_CLOSEDATE] / (24 * 60 * 60), 120)) OVER (PARTITION BY [TXT_TICKETNUMBER] ORDER BY [DAT_CLOSEDATE]) AS [CLOSE_DATE]
INTO 
    #Temp
FROM
    OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE, TXT_TICKETNUMBER FROM SCHEME.TABLE')
WHERE       
    [DAT_CLOSEDATE] = DATEADD(d, -1, GETDATE()) 

一切正常，但是SQL Server语法中的问题在WHERE子句中，因此考虑以下因素，我希望将其放入OPENQUERY内:

Everything is working as expected, but the problem in the SQL Server syntax is within the WHERE clause, so that I want to make it inside the OPENQUERY considering the following:

1. DAT_CLOSEDATE必须动态获取昨天的数据(今天1)

1. DAT_CLOSEDATE must be dynamic fetching the yesterday data (today-1)

Oracle中的DAT_CLOSEDATE列数据类型在SQL Server中为FLOAT和DATETIME

The DAT_CLOSEDATE column data type in Oracle is FLOAT and DATETIME in SQL Server

我希望OPENQUERY语法类似于以下内容:

I expect OPENQUERY syntax to be something like the following:

OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE, TXT_TICKETNUMBER   
                      FROM SCHEME.TABLE 
                      WHERE [DAT_CLOSEDATE] = DATEADD(d, -1, GETDATE())')

推荐答案

假设 DAT_CLOSEDATE 列具有浮点类型值，例如 20181231202534，20181231202713. .. ，即它们是类型 yyyymmddhh24miss 从日期值转换而成，其中yyyy代表year，mm代表月，dd代表日，hh24表示小时，范围为00-23，mi表示分钟，ss表示秒.

Suppose DAT_CLOSEDATE column has float type values such as 20181231202534, 20181231202713 ... that is, they're of type yyyymmddhh24miss converted from a date value, where yyyy stands for year, mm for month, dd for day, hh24 for hour in the range of 00-23, mi for minute, and ss is for second.

在这种情况下，可能会调用以下块:

OPENQUERY(ORACLE_DB, 'SELECT DAT_CLOSEDATE, TXT_TICKETNUMBER   
                        FROM SCHEMA.MYTABLE 
                       WHERE substr(DAT_CLOSEDATE,1,8) = to_char(sysdate-1,''yyyymmdd'')')

以获取前一天的值.

这篇关于OPEN QUERY中的WHERE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！