on 02-08-2016 2:41 PM
Hi All
I've used Oracle in the past, however I don't believe I went into so much detail in creating a prompt such as the following:
So the business requirement is to create a Today's Date Prompt as well as the yesterday's date prompt (Retrieving yesterday's data)
@Select(Class Name\Object Name) = (CASE WHEN @Prompt('Enter Date','D',{'Today'},mono,free,not_persistent,{'Today'}) THEN Cast(Convert(varchar(10), GetDate(), 112) as datetime) ELSE @Prompt('Enter Date','D',{'Today'},mono,free,not_persistent,{'Today'}) END )
At the moment I am receiving the following error:
I have researched this error message and came up with the following:
ORA-00920: invalid relational operator tips
Within the following URL, it mentions that I must use the = operator and if not used properly I will receive the following error message as stated above.
Any input would be greatly appreciated.
Kind Regards,
There is an oracle in-house built in function which was created by my DBAs to create the logic as such:
Schema.column_Name('Today')
for bringing in yesterday's dynamic prompt date by default:
Schema.column_Name('Yesterday')
If more details are required for this function please let me know.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Umair,
{TODAY} won't work, try this blog.
Dave's Adventures in BI: How Can I Make 'Today' My Default Prompt value
Thanks,
Mahboob Mohammed
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Mahboob
Thank you for your reply. Regarding whether a {Today} Default value prompt will work or not. I have actually been successful at creating this within a Universe having a SQL Server DB. The thing is my syntax is not compatible with an Oracle DB. I can provide screenshots of this @Prompt function working for BOE reporting to confirm this.
Kind Regards,
Umair Aleem
Hi Umair,
What version of BO are you using, where it was working with SQL Server DB?
Also, different databases use different syntax, so if a syntax works with a type of DB, that same syntax may/may not work with a different type of database.
Why don't you try and give a shot to the solution in the link I provided? If you have concerns or you don't want to go that route, let us know.
Also, can you provide screenshot for the filter/prompt definition and the parsing screen with the SQL Server DB?
Thanks,
Mahboob Mohammed
Hi Umair,
You are trying to create the prompt for Oracle database but you are using the functions of MSSQL server. Convert(varchar(10), GetDate(), 112 is sql server function.
Change this function specific to Oracle syntax and try parsing the prompt.
You need to use Sysdate function instead of getdate function for oracle.
And Use to_char, to_date functions to convert the default date to the required format.
User | Count |
---|---|
87 | |
10 | |
10 | |
9 | |
7 | |
6 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.