当我从DBMS执行我的Oracle查询时，它返回正确的数据。但是当我从nodejs oracledb代码中运行相同的查询时，结果是错误的。我到处都找过了。但没有得到任何解决方案。请帮帮我OracleDB版本是5.3.0。我也在下面提到了我的疑问。
SELECT
ordersl3.*
FROM
(
SELECT
ordersl2.*,
(
SELECT
COUNT(*)
FROM
(
SELECT
to_date(ordersl2.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS') + ROWNUM - 1 AS cal_date
FROM
all_objects
WHERE
ROWNUM <= to_date(ordersl2.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS') - to_date(ordersl2.orderrelentrydate,
'YYYY-MM-DD HH24:MI:SS') + 1
)
WHERE
to_char(cal_date, 'DY', 'NLS_DATE_LANGUAGE=AMERICAN') IN ( 'SAT', 'SUN' )
) AS order_confirmation_week_end_count
FROM
(
SELECT
ordersl1.obrowid,
MAX(ordersl1.ponumber) AS ponumber,
MAX(ordersl1.customerpartnum) AS customerpartnum,
MAX(ordersl1.sourcepartnum) AS sourcepartnum,
MAX(ordersl1.poreceiveddate) AS poreceiveddate,
MAX(ordersl1.poactioneddate) AS poactioneddate,
MAX(ordersl1.reviseddeliverydate) AS deliverydate,
MAX(ordersl1.orderqunatity) AS orderqunatity,
MAX(ordersl1.shadedescription) AS shadedescription,
MAX(ordersl1.shiptodesc) AS shiptodesc,
MAX(ordersl1.globalcustomercode) AS globalcustomercode,
MAX(ordersl1.globalsubbrandcode) AS globalsubbrandcode,
MAX(ordersl1.orderstatus) AS orderstatus,
MAX(ordersl1.companynum) AS companynum,
MAX(ordersl1.companyid) AS companyid,
MAX(ordersl1.companyname) AS companyname,
MAX(ordersl1.ordernum) AS ordernum,
MAX(ordersl1.orderlinenum) AS orderlinenum,
MAX(ordersl1.orderrelnum) AS orderrelnum,
MAX(ordersl1.ordervalue) AS ordervalue,
MAX(ordersl1.sellingunitprice) AS sellingunitprice,
MAX(ordersl1.expecteddeliverydate) AS expecteddeliverydate,
MAX(ordersl1.acknowledgedeliverydate) AS acknowledgedeliverydate,
MAX(ordersl1.orderrelentrydate) AS orderrelentrydate,
MAX(ordersl1.orderconfirmdate) AS orderconfirmdate,
MAX(mc.custname) AS custname,
MAX(ms.subbrandname) AS subbrandname,
MAX(ordersl1.order_confirmation_diff) AS order_confirmation_diff,
MAX(ordersl1.order_confirmation_diff_hours) AS order_confirmation_diff_hours,
MAX(ordersl1.po_actioned_hours_diff) AS po_actioned_hours_diff,
MAX(order_confirmation_holidays_count) AS order_confirmation_holidays_count,
MAX(wsf.dispatcheddate) AS dispatcheddate,
MAX(wsf.invoicedate) AS invoicedate,
MAX(wsf.customeracknowdate) AS customeracknowdate
FROM
(
SELECT
raworders.*,
( raworders.order_confirmation_diff * 24 ) AS order_confirmation_diff_hours,
( raworders.po_actioned_diff * 24 ) AS po_actioned_hours_diff
FROM
(
SELECT
wof.*,
(
SELECT
to_date(wof.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS') - to_date(wof.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS')
FROM
dual
) AS order_confirmation_diff,
(
SELECT
COUNT(*)
FROM
blabs.t_holiday th
WHERE
th.globalplantcode = wof.globalplantcode
AND th.active = 1
AND th.holiday BETWEEN to_date(wof.orderrelentrydate, 'YYYY-MM-DD HH24:MI:SS') AND to_date(
wof.orderconfirmdate, 'YYYY-MM-DD HH24:MI:SS')
) AS order_confirmation_holidays_count,
(
SELECT
to_date(wof.poactioneddate, 'YYYY-MM-DD HH24:MI:SS') - to_date(wof.poreceiveddate, 'YYYY-MM-DD HH24:MI:SS')
FROM
dual
) AS po_actioned_diff
FROM
sales.w_orderbook_f wof
WHERE
wof.orderrelentrydate != TO_DATE('1901-01-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
) raworders
) ordersl1
LEFT JOIN masterdata.mst_customer mc ON ordersl1.globalcustomercode = mc.custid
LEFT JOIN masterdata.mst_subbrand ms ON ordersl1.globalsubbrandcode = ms.subbrandcode
LEFT JOIN sales.w_salesinvoice_f wsf ON ordersl1.ordernum = wsf.ordernum
AND ordersl1.orderlinenum = wsf.orderline
AND ordersl1.orderrelnum = wsf.orderrelnum
WHERE
ordersl1.poactioneddate BETWEEN TO_DATE('2022-03-04 18:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2022-06-04 23:59:59',
'YYYY-MM-DD HH24:MI:SS')
AND ordersl1.orderstatus = 0
AND mc.custid = 'B00019-T'
AND upper(concat(concat(TRIM(ordersl1.ponumber), ' '), concat(ordersl1.shiptodesc, concat(to_char(ordersl1.poreceiveddate),
concat(ordersl1.customerpartnum, concat(ordersl1.sourcepartnum, concat(ordersl1.subbrandname, concat(mc.custname,
' ')))))))) LIKE '%%'
AND ordersl1.obrowid IS NOT NULL
GROUP BY
ordersl1.obrowid
ORDER BY
dispatcheddate
) ordersl2
) ordersl3
WHERE
( ordersl3.order_confirmation_diff >= 0
OR ordersl3.order_confirmation_diff < 0 )
AND ( ( ordersl3.orderconfirmdate IS NOT NULL
AND ( ordersl3.order_confirmation_diff - ( ordersl3.order_confirmation_holidays_count + ordersl3.order_confirmation_week_end_count ) *
24 ) <= 48 )
OR ( ordersl3.orderconfirmdate IS NULL
AND ( ( ( to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS') - ordersl3.orderrelentrydate ) * 24 ) - ( ( ordersl3.order_confirmation_holidays_count +
ordersl3.order_confirmation_week_end_count ) * 24 ) ) <= 48 ) )
AND ( ( to_date(ordersl3.poactioneddate, 'YYYY-MM-DD HH24:MI:SS') - to_date(ordersl3.poreceiveddate, 'YYYY-MM-DD HH24:MI:SS') ) *
24 <= 24 )
AND ( ( ordersl3.dispatcheddate IS NOT NULL
AND ordersl3.dispatcheddate <= ordersl3.deliverydate )
OR ( ordersl3.dispatcheddate IS NULL
AND to_date(sysdate, 'YYYY-MM-DD HH24:MI:SS') <= ordersl3.deliverydate ) )**[UPDATE]**过滤nodejs oracledb输出中的4400037844 PONUM（4400037844存在但不可能存在）
在DBMS中的直接查询中过滤4400037844 PONUM（4400037844不存在。结果正确）
[使用相同的查询，计数也不同]。oracledb nodejs
DBMS直接查询
[更新2]我找不到任何解决方案。所以我不得不做一个单独的java spring Boot 项目来运行这个查询。结果与DBMS直接查询完全相同。
所以大量的测试证明了我在节点oracledb中有一个bug。但我不能说那到底是什么。我认为有一个查询解释问题。所以结论是我不能推荐nodejs oracledb运行复杂的查询，因为结果是意外的，直到他们修复它。
1条答案
按热度按时间v6ylcynt1#
你的错误是在函数
to_date中使用了to_date(sysdate, ".....")，第一个参数是varchar2，但sysdate是这里的日期输入代码。Oracle使用会话nls参数自动将date转换为varchar2。node-oracledb和DBMS中的会话结果因此应用程序中的默认会话nls参数不同而不同。而to_date的结果则不同。我在实践中犯了这个错误：）