I have created a DEFINE that calls an Oracle function. I am...

[Todd Van Valkenburg]

I have created a DEFINE that calls an Oracle function. I am getting an error

NON OPTIMIZABLE EXPRESSION WITH SQL SPECIFIC SYNTAX

I am trying to figure out what this error means and how to resolve. I see that you can use DB_EXPR to call a sql in addition to SQL.function-name. I tried these two DEFINEs one at a time and placed them as BY statements:

test1/A1=SQL.f_is_peer_measurement_all_nulls ( OPERATOR, MEASUREMENT_CODE, PEER_GRP ,YEAR );

test2/A1=DB_EXPR(f_is_peer_measurement_all_nulls(OPERATOR,MEASUREMENT_CODE,PEER_GRP,YEAR));

The main data source for the report is a BINARY hold table. The Oracle user that connects to the DB has execute access to this function (I can sign on as that user and select from that function). The function returns a T or F. The input parameters are fields in the hold table (not defines).

Any suggestions on troubleshooting something like this

Todd

[Ron Moore]

Hi Todd. I think the problem may be the HOLD file. From the doc, the source file must have a relational suffix for DB_EXPR:

Reference: Usage Notes for the DB_EXPR Function

 

The expression must return a single value.

Any request that includes one or more DB_EXPR functions must be for a synonym that has a relational SUFFIX.

Field references in the native SQL expression must be within the current synonym context.

The native SQL expression must be coded inline. SQL read from a file is not supported.

[Todd Van Valkenburg]

Thanks Ron. Do you think that is also true for the first test1 using SQL. To get around this, is there a hold format type (not BINARY) that would work with either SQL or DB_EXPR

[John Gelona]

Short answer is no. This is not an error, just a warning. The only time this and similar warnings are not displayed is when joining tables in the same database. Any other join combination will get this warning. It is just to let you know that performance may be impacted.

[Gail North]

Hi Todd,

Would it possibly be more efficient to embed the function call within the hold file itself Ive done that a number of times, not necessarily to get around the SQL. problem, but just to improve the performance of the report.

[Todd Van Valkenburg]

Unfortunately, this is an error and not just a warning. I.e. No output if I use this DEFINE variable as a BY or filter.