Creating a Dynamic Prompt (Today's Date and Yesterday's Date) in Oracle

[Former Member]

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,

[Former Member · Accepted solution]

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.

[mhmohammed]

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

[Former Member]

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

[mhmohammed]

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

[Former Member]

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.

[mhmohammed]

You're correct Divya, thanks for chiming in.

Mahboob Mohammed

[Former Member]

Hi Mahboob,

Good to know that. If you want close this thread as answered