XI 4.2 . Database Oracle.
We are using free hand sql for our reports . In the where clause we are using runtime prompts and one of the conditions is to use a function which has 5 input parameters. In my report this has to be an optional prompt viz. func(Table1.column,Hard-Coded,Table1.column,Table1.column,Hard-Coded)> @prompt(,).
I used the below logic:
@Prompt(‘Enter Value (optional):’, ‘A’,{’%’} , Mono, Free)=’%’ or func(Table1.column,Hard-Coded,Table1.column,Table1.column,Hard-Coded)>@Prompt(‘Enter Value (optional):’, ‘A’,{‘ALL’} , Mono, Free)
The problem here is when I put % at runtime it gives the desired output and ignores the function and fetches all rows . However when I enter ALL it throws an oracle error ORA-01722 : invalid number. What is going wrong here?
Hi,
I am impressed… what a brutal function/nality can be put into @prompt…
nevertheless… just a remark i ve noticed - please pardon my knowledge. - ideas:
- the @prompt “A” - is used for alfanumerical/text and according the ora error - it expects a number should nt be there operated with “N” ?
- there should be some possibility to catch/see what the db gets from BO… oracle admin told me its possible to see what sql query is get from BO and inspect what is there vs what we think is there…
It is brutal indeed ,but that is the requirement of our users .
I came up with the below prompt, the requirement is that this prompt should be optional so if no value is entered into this prompt then it should fetch all the values which could be null ,-ve and +ve.however if a user gives a value eg.100 ,at report runtime, then this should fetch values greater than 100
fn_conv(Col1, ‘USD’, Col2, Col3, ‘Abc’)) =
decode(@prompt(‘17. Enter Grosss:’,‘N’,{0},MONO,FREE,USER:1),0,fn_conv(Col1, ‘USD’, Col2, Col3, ‘Abc’))
or
fn_conv(Col1, ‘USD’, Col2, Col3, ‘Abc’)) >
@prompt(‘17. Enter Grosss:’,‘N’,{0},MONO,FREE,USER:1)
However, if a user wants values greater than 0 then is there a way to handle it using prompts. Any better way of handling this.