node.js – Nodejs oracledb different result with query executer and direct query from DBMS

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)

enter image description here

[COUNTS are also different using the same identical query]. oracledb nodejs
enter image description here

DBMS Direct Query
enter image description here

[UPDATE 2]

I cannot find 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 what 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. It’s better if the contributors have a look on this matter.

Another important thing is in the comment section i’ve replied to the Christopher Jones’s last comment. But now its deleted by someone anyhow. Christopher Jones is a Senior Principal Product Manager at Oracle & the contributor of node oracledb package. Here i’ll post that comment’s screenshot.

enter image description here

Leave a Comment