Evaluate function can't handle arguments with different data types
I have developed a custom function in the Oracle database, called Inflated, which takes two arguments. First argument is a number, second argument is a date.
.
I'm attempting to use the Evaluate function in OBIEE to reference that custom database function.
.
This Evaluate function cannot be compiled in the Expression Builder:
EVALUATE('inflated(%1,%2)', "Global"."Physical Facts"."Sales", "Global"."Physical Facts"."Month_End_Date" )
[nQSError: 22027] Union of non-compatible types.
.
Neither can this one (same error message):
EVALUATE('inflated(%1,%2)', "Global"."Physical Facts"."Sales", "Global"."Product"."Item")
.
Why? Because the datatypes of the two arguments aren't the same. In the first example, the argument datatypes are Number and Date. In the second example, the argument datatypes are Number and Varchar. Non-compatible types.
.
This Evaluate function CAN be compiled in the Expression Builder, because the two aguments are both Numbers:
EVALUATE('inflated(%1,%2)', "Global"."Physical Facts"."Sales", "Global"."Physical Facts"."Cost" )
.
It also works for two Varchars or two Dates. As long as the arguments are of the same datatype, Evaluate works. Otherwise, it's a no-go.
Can someone please tell me why in the world this should be the case? Custom database functions can accept all sorts of arguments, yet it seems that we can't pass those arguments to them OBIEE in the Evaluate function unless all arguments are of the same datatype. This makes no sense.