Categories
- All Categories
- Oracle Analytics and AI Learning Hub
- 54 Oracle Analytics and AI Sharing Center
- 26 Oracle Analytics and AI Lounge
- 311 Oracle Analytics and AI News
- 57 Oracle Analytics and AI Videos
- 16.4K Oracle Analytics and AI Forums
- 6.7K Oracle Analytics and AI Labs
- Oracle Analytics and AI User Groups
- 117 Oracle Analytics and AI Trainings
- 24 Oracle Analytics and AI Challenge
- Find Partners
- For Partners
Dyn Filter; Trying to Retrieve 13 Weeks of Data from EPM Dataset Using BETWEEN Clause
Hello Oracle Community,
I am working on a project where I am utilizing a dataset from Oracle Enterprise Performance Management (EPM) Cloud. I am using a direct connection to access the data, hence I don't have the privilege to alter the data at source. In the dataset, time information is stored as text/string, which necessitates a conversion to a date or timestamp format (I've tried date format as well) for my analysis. My objective is to retrieve 13 weeks of data based on a selected date.
Here's the formula I am using to filter the data:
CAST("Period"."Period Name Gen 6" AS TIMESTAMP)
BETWEEN
TIMESTAMPADD(SQL_TSI_DAY, -91, CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP))
AND CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP)
However, when executing this query, I am encountering a syntax error near the <BETWEEN> clause with error code [nQSError: 26012]. I have triple-checked the syntax but can't seem to pinpoint the issue.
Could anyone provide insights or suggestions on what might be wrong with the query, or how to correctly perform this operation given the constraints?
Thank you in advance for your assistance!
Answers
-
Hi user_WM6I1 ,
Seems to be an issue with below function conversion.
TIMESTAMPADD(SQL_TSI_DAY, -91, CAST(@parameter("SelectedDate")('2022-12-31') AS TIMESTAMP))
You can try below format
Syntax -
cast(TIMESTAMPADD(SQL_TSI_HOUR,-6 ,CURRENT_TIMESTAMP ) as date)
Regards,
Arjun
0 -
Hi,
Thanks Arjun, So you are saying it should look more like this:
CAST("Period"."Period Name Gen 6" AS DATE)
BETWEEN @parameter("SelectedDate")('2022-12-31') AND CAST(TIMESTAMPADD(SQL_TSI_DAY, -91, CURRENT_TIMESTAMP) AS DATE)
If so....still getting this:
Validation of parameter values failed. Near <BETWEEN>: Syntax error [nQSError: 26012] .
0 -
Hi,
Seems like values are not calculating correctly. Your expression should like below so that it will validate.
'SYSDATE' BETWEEN (@parameter("Date")('05/08/1951')) AND 'sysdate'
Take one by one and calculate the values, After that you can put everything in Expression filter.
Regards,
Arjun
0 -
Hi,
It definitely works in the Expression Filter...thanks.
CAST("Period"."Period Name Gen 6" AS DATE) BETWEEN CAST(@parameter("SelectedDate")('2022-12-31')AS DATE) AND CAST(TIMESTAMPADD(SQL_TSI_DAY, -91, CURRENT_TIMESTAMP) AS DATE)
Added it to my filter section, and now I can see values in my parameters. The Period Name is breaking the report now. I need to troubleshoot that....but at least that works....thank you!
1 -
HI User_UXDSV ,
Good to see your update , Let me know if you still need some help on this formula i can try in my local system and get back to you with updated expression .
Regards,
Arjun
1 -
Hi @User_UXDSV
Are you able to resolve the issue?
To enhance your experience and foster meaningful interactions, we encourage you to personalize your profile by setting up a display name and uploading a profile picture. Your display name will be how others recognize and engage with you in discussions, while a profile picture adds a personal touch to your forum presence. Take a moment to update your profile with a display name and an image representing you. Let's create a vibrant and engaging community together!
Regards,
Arjun
0
