我直接作用于表的查询在写入视图时不起作用 [英] My query that works directly to a table does not work when written to a view

问题描述

Oracle 12c，ColdFusion 2018

Oracle 12c, ColdFusion 2018

我在两个不同的数据库中有两个相同的表.有一个视图使用 UNION 将数据连接到一个数据集，使用数据库链接.该视图有效并且没有错误.

I have two identical tables in two different databases. There is a view that uses UNION to join the data into one data set, using a database link. The view works and has no errors.

当我从 ColdFusion 查询视图时，我收到无效月份"错误.

When I query the view from ColdFusion I get a "not a valid month" error.

当我只查询其中一个表(而不是视图)时，我没有得到同样的错误.

When I query just one of the tables (and not the view), I do not get the same error.

知道为什么会发生这种情况吗?

Any idea why this would happen?

Table1 in database1
EmployeeID (number)
ShiftCode (varchar2)
ShiftTime (date)

Table2 in database2
EmployeeID (number)
ShiftCode (varchar2)
ShiftTime (date)


ViewBothTables  - Table1 and table2 created in database1
SELECT EmployeeID, ShiftCode, ShiftTime
FROM Table1
UNION
SELECT EmployeeID, ShiftCode, ShiftTime
FROM USER.Table2@databaseConnection2


Query1 in ColdFusion - This works - returns 48 rows
SELECT employeeID, ShiftCode, ShiftTime
FROM USER.table1@databaseConnection1
Where ShiftTime <= #thisSchedWeekEnd#
            AND
      ShiftTime >= #thisSchedWeekStart# 

Query2 in ColdFusion - This works - returns 10 rows
SELECT employeeID, ShiftCode, ShiftTime
FROM USER.table2@databaseConnection2
Where ShiftTime <= #thisSchedWeekEnd#
            AND
      ShiftTime >= #thisSchedWeekStart# 

Query 3 in ColdFusion - this gives me the error
SELECT employeeID, ShiftCode, ShiftTime
FROM USER.viewBothTables@databaseConnection1
Where ShiftTime <= #thisSchedWeekEnd#
            AND
      ShiftTime >= #thisSchedWeekStart# 


[Macromedia][Oracle JDBC Driver][Oracle]ORA-01843: not a valid month ORA-02063: preceding line from databaseConnection1

结构相同，Table1和Table2是两个不同数据库中相同表(不同人)的副本.该视图有效，并且是可见的(哈哈)我正在从第三个数据库查询.ColdFusion 日期变量在所有三个示例中都是相同的，因为它三次都是相同的变量.前两个查询有效.第三个说无效月份.

The structures are identical, Table1 and Table2 are copies of the same tables (with different people) in two separate databases. The view works, and is viewable (ha ha) I am querying from a third database. The ColdFusion date variable is identical in all three examples, because it's the same variable all three times. The first two queries work. The third says invalid month.

我在页面上连续进行了所有三个查询，与上面完全一样.前两个返回数据，第三个错误输出.

I have all three queries in a row on the page, exactly as above. The first two return data, the third errors out.

是的，设置很愚蠢.我对此无能为力，它们是继承的系统，我无法更改，只能处理.

Yeah, the setup is stupid. I can't do much about it, they are inherited systems that I can't change and just have to deal with.

推荐答案

我认为 databaseConnection1 和 databaseConnection2 的日期格式是不同的.

I think the date format is different between databaseConnection1 and databaseConnection2.

这就是为什么对两个不同数据库的单独查询有效而对一个数据库的组合查询无效的原因.

That's why the individual query on the two different databases work and the combined query on one database doesn't work.

能否请您检查两个数据库中NLS_DATE_FORMAT 的下表.

Can you please check the below table in both databases for NLS_DATE_FORMAT.

    select * from nls_session_parameters;

更新 1:-由于两个数据库的日期格式相同，请尝试对如下参数进行日期格式的显式转换

Update 1:-Since the date format are same for the two databases try explicit conversion of date format for the parameter like below

  SELECT employeeID, ShiftCode, ShiftTime
  FROM USER.viewBothTables@databaseConnection1
   Where ShiftTime <= #dateFormat(thisSchedWeekEnd, "mm/dd/yyyy")#
        AND
   ShiftTime >= #dateFormat(thisSchedWeekStart, "mm/dd/yyyy")#

这篇关于我直接作用于表的查询在写入视图时不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！