In this blog post, let’s learn about the error message “4118 The cursor query expression is not updatable.” when working with HPE NonStop SQL/MX, the reason for the error and the solution to fix this error message.
Error Message
4118 The cursor query expression is not updatable.
Reason for the Error
You specified a join or a nonupdatable query in an updatable cursor query (that is, one with an optional FOR UPDATE OF clause). A join query is not updatable.
Solution
An updatable cursor query should not specify a join or a nonupdatable query. An SQL/MX statement cursor is updatable if all the following are true:
It is a SELECT statement.
There is only one table reference in the FROM clause, and there are no correlated subquery references to that table. For example, this query is updatable:
SELECT A FROM T;
This one is not:
SELECT A FROM T WHERE B=(SELECT C FROM U WHERE T.I.=U.I)
There are no aggregates.
There are no GROUP BY, DISTINCT, or ORDER BY clauses.
All select_list columns are column references.
No column reference occurs more than once in the select list.
This example of a nonupdatable cursor join query selects the target of the update correctly and receives an error:
>>SELECT A.PROD_CODE, B.UPDATE_FLAG FROM EXPRODPARAMS A, MSRATES B
+>WHERE CASE WHEN B.UPDATE_FLAG IS NULL THEN ‘N’ ELSE B.UPDATE_FLAG END=’N’
+>AND A.ACC_TYPE=B.ACC_TYPE AND A.PROD_CODE=B.PROD_CODE
+>FOR UPDATE OF UPDATE_FLAG ;
*** ERROR[4118] The cursor query expression is not updatable.
*** ERROR[8822] Unable to prepare the statement
