The following occurs when executing an equation engine that uses a callable SQL (with a custom function), or callable SQL contains a custom view
In Equation Engine, you need to have call parms such that you have 2 more than the number of binds and selected fields - so if you have no binds and
are only returning one field in the select - you would need 3 call parms (1 for sql return code, one for sql row count, one stem to catch the returning
field in your sql). However, if in your select statement in your SQL you use something like a substring function - then the equation will fail when you
test it with an error returned that you don't have the correct number of calls parms.
So, if your sql statement was
SELECT substr(A.STRM,1,2)
FROM ps_session_table
or
SELECT ITEM_AMT FROM PS_CUSTOM_VW WHERE EMPLID=:1 AND ADMIT_TERM=:2
You would expect to need 3 bind variables - 2 for sql return codes/row, and a stem to catch the select. However, the equation engine will error your
equation because it is counting each comma in your select as needing a new stem.
The available workaround is to create a TABLE that can be used to simplify the logic of the equation SQL. Insert the data in it and then reference that table instead - using a simpler SQL.
No comments:
Post a Comment