Forum Stats

  • 3,768,508 Users
  • 2,252,800 Discussions
  • 7,874,600 Comments

Discussions

Query in sql commands gives different value than in my application

I have a query that looks like this:

SELECT c.tuesday_10_00
FROM
(
SELECT
COUNT(ID) tuesday_10_00
FROM FRESHDESK_RAPPORTAGE
WHERE TRUNC(SYSDATE, 'iw') + 1 + 10/24
LIKE APEX$ROW_SYNC_TIMESTAMP + 1/24 
) c
WHERE TRUNC(SYSDATE) >= TRUNC(SYSDATE, 'iw') + 1

This returns the value 55:

which is the correct one but when I use this query in my application it gives the value 112.

I have no idea why this is happening.

I'm on Oracle apex 20.2.0.00.20.

Answers

  • InoL
    InoL Member Posts: 9,328 Gold Crown

    Just a guess, because it's hard to understand this query and we don't know what's in FRESHDESK_RAPPORTAGE. Looks like the number of Freshdesk incidents on tuesday 10.00h?

    The query in SQL Workshop runs with the time zone of the database. In your application, this may be different. Check the application globalization settings for Automatic Time Zone:

    Controls the setting of the database session time zone. When set to Yes, the client time zone is derived from the client's Web browser and set for the duration of the Application Express session. Subsequent page views will have the database session time zone set properly per page view. Once set, this setting can be overridden using APEX_UTIL.SET_SESSION_TIME_ZONE, or reset using APEX_UTIL.RESET_SESSION_TIME_ZONE.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,909 Red Diamond

    Look at:

    WHERE TRUNC(SYSDATE, 'iw') + 1 + 10/24
    LIKE APEX$ROW_SYNC_TIMESTAMP + 1/24 
    

    First of all LIKE makes no sense here since you are comparing dates. Secondly it is NLS dependent. LIKE compares strings, so both TRUNC(SYSDATE, 'iw') + 1 + 10/24 and LIKE APEX$ROW_SYNC_TIMESTAMP + 1/24 are implicitly converted from DATE/TIMESTAMP to string using session NLS_DATE_FORMAT/NLS_TIMESTAMP format. So most likely application NLS_DATE_FORMAT/NLS_TIMESTAMP formats are different from SQL*Plus session and you get different results. Anyway, get rid of LIKE and use corresponding DATE/TIMESTAMP comparison operators.

    SY.

    InoL