Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
BIPS Data Model - how to handle a date parameter with a text based list of values
BIP Publisher in Fuision Financials - data model problem - so OBIA version 11.1.1.9 as my issue is entirely in the cloud.
I am trying to work around a prebuilt data model which uses a package to populate its data, so there is a lot that I cannot touch / change.
I need to use an existing prompt with LOV to apply an extra date condition on the data model.
The date is not held as a date in the LOV, it is a period selector in the format 'Nov-2008' - so text.
I have read that to reference the existing parameter I just need to use the syntax &MyPeriodSelector - I know I need to convert my text into a date to allow meaningful comparison
The problem I have is when I try this and put in the value Nov-2008 it errors saying the below; -
oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00904: "NOV": invalid identifier
and prd.calendar_period_open_date <= to_date(&MyPeriodSelector,'Mon-YYYY')
Can anyone tell me the right syntax to use for this please, I have experimented for some time, but each time I just get a subtly different kind of error.
I also tried with ' ' around the parameter name but then it just treats it as a literal.
thank you,
Teresa X.
Answers
-
Anyone provide any help at all on this please?
In brief;
Text financial period passed via parameter with format "Apr-2018"
I need to change that value into a date in a data model.
Can anyone tell me what syntax I need to achieve this; -
to_date(&MyPeriodSelector,'Mon-YYYY')
0 -
I use the below and it does not recognise the variable as a variable
and nvl(prd.calendar_period_close_date,sysdate+1) >= cast('01-'||'&MyPeriodSelector' as date)
I remove the speech marks around the variable name and it sees the month from the full variable as a name and errors.
and nvl(prd.calendar_period_close_date,sysdate+1) >= cast('01-'||&MyPeriodSelector as date)
0 -
Anyone please - anything?
My latest; -
and nvl(prd.calendar_period_close_date,sysdate+1) >= to_date('01-'||nvl(:P_PERIOD_FROM,'Jan-1951'), 'DD-Mon-YYYY')
I have reviewed the links that talk about the canonical date format, but as this is entirely within SQL and I am not starting with a date parameter and I am using to_date and a date format then this should work??
0 -
Anyone at all with even half a clue on this or at least confirmation that it cannot be done - please?
0 -
Still hoping there is an answer on text to date in BIPS?
0 -
The manual tells me this
{
Working with Date Parameters
Oracle BI Publisher always binds date column or date parameter as a timestamp object.
To avoid timestamp conversion, define the parameter as a string and pass the value with formatting as 'DD-MON-YYYY' to match the RDBMS date format.
}
This being true then if my text in the parameter is Feb-2019 and I prepend '01-' and then convert this into a date in sql, converting because I need to compare a range, then this should work, but still it does not.
and nvl(prd.calendar_period_close_date,sysdate+1) >= cast('01-'||'&MyPeriodSelector' as date)
Anyone tell me why please?!!
0 -
Sorry you have to work with BIP. I'm not exactly sure what your limitations are based on
I am trying to work around a prebuilt data model which uses a package to populate its data, so there is a lot that I cannot touch / change.
But I'm wondering if you can create a simple BIP data model and get it to bind your parameter correctly. For example,
select row_wid
from w_day_d
where 1=1
and calendar_date >= to_date('01-' || :MyPeriodSelector, 'DD-Mon-YYYY')
and calendar_date <= sysdate
And create the parameter MyPeriodSelector as follows
When I do this, I am able to get sample data returned and I'm able to create a dashboard:
0 -
Thanks for your reply sweetie, really very very kind of you.
Completely puzzled as following your set up perfectly but it does not work this way for me.
I can use sysdate to compare - good
I can use text to match to text - good
But the minute I try to convert text to a date - even though it matches the DD-Mon-YYYY using to_date - it errors with the invalid month message. This is the case even when I have to_date('01-Apr-2019','DD-Mon-YYYY').
Think maybe a bug and I will have to go for an SR.
My limitations - I cannot cook too good - but no one has complained about it yet ;-)
0 -
Have you tried turning it on its head?
You have it in text, why not turn it into a number; YYYYMMDD; this will take some case logic or similar in, conjunction with cast or similar.
Then turn your date on the source table to a number using to_char / to_number and see if it works for you comparing number with number.
It might be slow running, but at least if it works you will know for definite that you have some kind of date conversion error.
0 -
Your suggestion worked darling - but like you said it does make it take longer.
Thank you
Don't know why when elb666's suggestion works for him and mine is identical it won't work for me.
Teresa X
0