When I execute my oracle query from the DBMS it returns correct data. But when i run the same query from from the nodejs oracledb code, results are wrong. I've searched this everywhere. but didn't get any solution. please help me. oracledb version is 5.3.0. I've also mentioned my query below.
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]
Filter 4400037844 PONUMBER in the nodejs oracledb output (4400037844 is there but its cannot be)
Filter 4400037844 PONUMBER in the direct query in the DBMS (4400037844 is not there. results are correct)
[COUNTS are also different using the same identical query].
oracledb nodejs
DBMS Direct Query
[UPDATE 2] I cannot found any solution for this. So I had to make a separate java spring boot project to run this query. that resulted perfectly as same as the DBMS direct query.
So A lot of tests proved me that there is a bug in the node oracledb. But i cannot say whats that exactly. I think there is a query interpretation problem. So the conclusion is i cannot recommend nodejs oracledb to run complex queries since the results are unexpected until they fix it.
