Jump to content
The ibi Community has moved to a new platform: Please Sign In and choose Forgot Password to continue

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


Todd Van Valkenburg

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
  • Create New...