Todd Van Valkenburg Posted February 14, 2022 Share Posted February 14, 2022 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 More sharing options...
Ron Moore Posted February 15, 2022 Share Posted February 15, 2022 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 More sharing options...
Todd Van Valkenburg Posted February 15, 2022 Author Share Posted February 15, 2022 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 Link to comment Share on other sites More sharing options...
John Gelona Posted February 15, 2022 Share Posted February 15, 2022 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. Link to comment Share on other sites More sharing options...
Gail North Posted February 15, 2022 Share Posted February 15, 2022 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. Link to comment Share on other sites More sharing options...
Todd Van Valkenburg Posted February 15, 2022 Author Share Posted February 15, 2022 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. Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now