Categories
- All Categories
- 127 Oracle Analytics News
- 23 Oracle Analytics Videos
- 14.5K Oracle Analytics Forums
- 5.5K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 47 Oracle Analytics Trainings
- 7 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 8 Oracle Analytics Industry
- Find Partners
- For Partners
Need to validate TimeZone dynamically based on current region
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.
Answers
-
Nobody here can see your screen and know what you are doing, how, where.
You should consider providing a minimum of context. Things like a product name and a version to start with, where you tried to enter that expression etc.
0 -
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.
0 -
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 ).
0 -
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.
0
