HP NonStop SQL/MX - Page 217

Binder and Compilation Messages (4000 through
4999)
HP NonStop SQL/MX Messages Manual—640324-001
6-31
SQL 4118
Cause. 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.
Effect. The operation fails.
Recovery. 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.
4118 The cursor query expression is not updatable.