Need to validate TimeZone dynamically based on current region

Question

I am trying to pick up all Orders from previous day half past 4, but not able to validate, tried sample query without giving time yet as below

"Fact - Orders"."Order Date" >= (TO_CHAR(FROM_TZ(CAST(SYSDATE-1 AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London','DD-MM-YYYY HH24:MI:SS')).

this errors out with Error during query processing (SQLExecDirectW).State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. (HY000)State: HY000. Code: 43275. [nQSError: 43275] Message returned from OBIS [ecid:126ec485-6f6a-4f2f-b3a1-783a4c9f52c9-000c1ee8,0:1:6 ts:2024-05-15T12:57:26.338+00:00]. (HY000)State: HY000. Code: 27002. [nQSError: 27002] Near <AT>: Syntax error (HY000)State: HY000. Code: 26012. [nQSError: 26012] . (HY000)SQL Issued: {call NQSGetLevelDrillability('SELECT "Fact - Orders"."Order Number" saw_0, "Fact - Orders"."Line Number" saw_1, "Fact - Orders"."Order Date" saw_2 FROM "XXXX" WHERE "Fact - Orders"."Order Date" >= (TO_CHAR(FROM_TZ(CAST(SYSDATE-1 AS TIMESTAMP), ''UTC'') AT TIME ZONE ''Europe/London'',''DD-MM-YYYY HH24:MI:SS'')) ')}

request your inputs.

Viswanath-Oracle · Answer

Time Zone Value In OAC DV Workbook And Classic Analysis (Doc ID 2913696.1), there is an enhancement for this. not sure if it is resolved.

Gianni Ceresa · Answer

Ok, so you are on OAC and are trying to write that as a filter in a "classic" analysis.

The thing is that you are writing Oracle Database SQL syntax, but the filter in a "classic" analysis does speaks LSQL. You simply have a language mismatch: you are using the wrong syntax, the wrong languages.

In a filter you can only use the functions that exists in LSQL, and that language is the BI Server language, not the Oracle Database language.

Look at all the functions available in LSQL and try to write your filter using that language. If you edit a column formula there where you try to enter the filter expression, you have a list of all the existing LSQL functions (there are a handful more that aren't documented, but if they aren't documented it's maybe because we aren't supposed to use them :D ).

Viswanath-Oracle · Answer

Hi Gianni,

I am on OACS, Version 12.2.9.0.

I am trying to run an OACS visualization based on a filter condition to pick up only orders between previous day 4.30pm London UK time to current day 4.30pm London UK time.

tried using FROM_TZ(CAST(SYSDATE-1 AS TIMESTAMP), 'UTC') AT TIME ZONE 'Europe/London' but this function is not recognized in OAC.

Thanks.