In this blog post, let’s learn about the error message “4109 Sequence functions are placed incorrectly: name.” when working with HPE NonStop SQL/MX, the reason for the error and the solution to fix this error message.
Error Message
4109 Sequence functions are placed incorrectly: name.
Reason for the Error
You specified a query that includes a SEQUENCE BY clause that contains an illegally placed sequence function name. Sequence functions (such as RUNNINGSUM, MOVINGSUM, LASTNOTNULL) are supported only in the select list or the HAVING clause of the query expression containing the SEQUENCE BY clause. For example, these queries are legal:
>>select a, runningcount(b) from T1 sequence by a;
>>select x from (select a, runningcount(b) from T1 sequence by a) T2(x,y) where y > 10;
>>select count(*) from t1 sequence by b group by a having runningsum(a) > count(*);
Solution
Correct the syntax and resubmit. For example:
>>select a from T1 where runningcount(b) > 10 sequence by a;
*** ERROR[4109] Sequence functions placed incorrectly: RUNNINGCOUNT(CAT.SCH.HPPARTEST1.B).
*** ERROR[8822] Unable to prepare the statement
