I am using the System i Navigator in order to create a View. My view is very simple:
SELECT MOMAST.ORDNO, MOMAST.FITEM
FROM AMFLIBT.MOMAST AS MOMAST
WHERE MOMAST.FITEM LIKE 'POS-%'
GROUP BY MOMAST.ORDNO,MOMAST.FITEM
ORDER BY MOMAST.FITEM,MOMAST.ORDNO
When clicking on OK button to creat the view, I am getting the following error:
SQL0199] Keyword ORDER not expected. Valid tokens: . Cause . . . . . : The keyword ORDER was not expected here. A syntax error was detected at keyword ORDER. The partial list of valid tokens is .
When I remove the ORDER BY statement, the view is being created successfully.
I need to create my view with the ORDER BY statement, how can I achieve this task with no errors?
A view is a relational table, and the relational model defines a table as a set of rows. Since sets are not ordered - by definition - the rows in a view are not ordered, either. Therefore, an ORDER BY clause in the view definition is meaningless. The SQL standard (SQL:2003) does not allow an ORDER BY clause in a subselect in a CREATE VIEW statement, just as it is not allowed in a CREATE TABLE statement.
In your case, I recommend creating the view without the ORDER BY statement, and using this statement when selecting from the view (not while creating it).