DB2 - problem by creating view with select left join and order by..
Hi experts,
I have a problem at creating a view in DB2, although the select itself runs absolutely OK. Here is the commands:
1. runs OK, I become some records:
SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t
left join ISIWAS.TPKARTEN_TIERPARKLI
2. create with the same select fails:
CREATE VIEW ISIWAS.TPKARTEN_CSV2 AS SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t
left join ISIWAS.TPKARTEN_TIERPARKLI
Error message (in German, below I give it also in English):
CREATE VIEW ISIWAS.TPKARTEN_CSV2 AS SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t left join ISIWAS.TPKARTEN_TIERPARKLI
DB21034E Der Befehl wurde als SQL-Anweisung verarbeitet, da es sich um keinen
gültigen Befehl des Befehlszeilenprozessors handelte. Während der
SQL-Verarbeitung wurde Folgendes ausgegeben:
SQL20211N Die Angabe ORDER BY oder FETCH FIRST n ROWS ONLY ist ungültig.
SQLSTATE=428FJ
SQL20211N Die Angabe ORDER BY oder FETCH FIRST n ROWS ONLY ist ungültig.
Erläuterung:
ORDER BY und FETCH FIRST n ROWS ONLY ist in folgenden Kontexten nicht
zulässig:
* äußerer Fullselect einer Sicht
* äußerer Fullselect in der Anweisung RETURN einer SQL-Tabellenfunktion
* Definition einer MQT
* Subselect, der nicht in runde Klammern eingeschlossen ist
Benutzeraktion:
Im Fall von:
Subselect
Schließen Sie den Subselect, der ORDER BY oder FETCH FIRST n
ROWS ONLY enthält, in runde Klammern ein.
FETCH FIRST n ROWS ONLY
Verwenden Sie die Klausel ROW_NUMBER() OVER() zusammen mit
einem Vergleichselement in der Klausel WHERE. Beispiel:
SELECT name FROM
(SELECT
ROW_NUMBER() OVER() AS m, name
FROM emp
) AS e
WHERE m < 10
ORDER BY Verwenden Sie in der Abfrage die Klausel ORDER BY unter Angabe
der Sicht, der MQT oder der SQL-Tabellenfunktion.
sqlcode: -20211
sqlstate: 428FJ
English:
SQL20211N
The specification ORDER BY or FETCH FIRST n ROWS ONLY is invalid.
Explanation
An ORDER BY or FETCH FIRST n ROWS ONLY is not allowed in:
the outer fullselect of a view
the outer fullselect in the RETURN statement of an SQL Table function
a materialized query table definition
a subselect which is not enclosed in parenthesis
User response
In case of:
subselect
Enclose the subselect including the ORDER BY or FETCH FIRST n ROWS ONLY in parenthesis.
FETCH FIRST n ROWS ONLY
Use the ROW_NUMBER() OVER() clause in conjunction with a predicate in the where clause. Example:
SELECT name FROM
(SELECT
ROW_NUMBER() OVER() AS m, name
FROM emp
) AS e
WHERE m < 10
ORDER BY
Use ORDER BY in the query using the view, the materialized query table, or the SQL table function instead.
sqlcode: -20211
sqlstate: 428FJ
Can you help me to understand why and how to correct the command to create the view?
Thanx in advance!
Yuriy
I have a problem at creating a view in DB2, although the select itself runs absolutely OK. Here is the commands:
1. runs OK, I become some records:
SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t
left join ISIWAS.TPKARTEN_TIERPARKLI
2. create with the same select fails:
CREATE VIEW ISIWAS.TPKARTEN_CSV2 AS SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t
left join ISIWAS.TPKARTEN_TIERPARKLI
Error message (in German, below I give it also in English):
CREATE VIEW ISIWAS.TPKARTEN_CSV2 AS SELECT t.GUTSCHEINNR, t.K_ART, t.TIERPARK_ID FROM ISIWAS.TPKARTEN t left join ISIWAS.TPKARTEN_TIERPARKLI
DB21034E Der Befehl wurde als SQL-Anweisung verarbeitet, da es sich um keinen
gültigen Befehl des Befehlszeilenprozessors handelte. Während der
SQL-Verarbeitung wurde Folgendes ausgegeben:
SQL20211N Die Angabe ORDER BY oder FETCH FIRST n ROWS ONLY ist ungültig.
SQLSTATE=428FJ
SQL20211N Die Angabe ORDER BY oder FETCH FIRST n ROWS ONLY ist ungültig.
Erläuterung:
ORDER BY und FETCH FIRST n ROWS ONLY ist in folgenden Kontexten nicht
zulässig:
* äußerer Fullselect einer Sicht
* äußerer Fullselect in der Anweisung RETURN einer SQL-Tabellenfunktion
* Definition einer MQT
* Subselect, der nicht in runde Klammern eingeschlossen ist
Benutzeraktion:
Im Fall von:
Subselect
Schließen Sie den Subselect, der ORDER BY oder FETCH FIRST n
ROWS ONLY enthält, in runde Klammern ein.
FETCH FIRST n ROWS ONLY
Verwenden Sie die Klausel ROW_NUMBER() OVER() zusammen mit
einem Vergleichselement in der Klausel WHERE. Beispiel:
SELECT name FROM
(SELECT
ROW_NUMBER() OVER() AS m, name
FROM emp
) AS e
WHERE m < 10
ORDER BY Verwenden Sie in der Abfrage die Klausel ORDER BY unter Angabe
der Sicht, der MQT oder der SQL-Tabellenfunktion.
sqlcode: -20211
sqlstate: 428FJ
English:
SQL20211N
The specification ORDER BY or FETCH FIRST n ROWS ONLY is invalid.
Explanation
An ORDER BY or FETCH FIRST n ROWS ONLY is not allowed in:
the outer fullselect of a view
the outer fullselect in the RETURN statement of an SQL Table function
a materialized query table definition
a subselect which is not enclosed in parenthesis
User response
In case of:
subselect
Enclose the subselect including the ORDER BY or FETCH FIRST n ROWS ONLY in parenthesis.
FETCH FIRST n ROWS ONLY
Use the ROW_NUMBER() OVER() clause in conjunction with a predicate in the where clause. Example:
SELECT name FROM
(SELECT
ROW_NUMBER() OVER() AS m, name
FROM emp
) AS e
WHERE m < 10
ORDER BY
Use ORDER BY in the query using the view, the materialized query table, or the SQL table function instead.
sqlcode: -20211
sqlstate: 428FJ
Can you help me to understand why and how to correct the command to create the view?
Thanx in advance!
Yuriy
Who is Participating?
Hi Kent,
you are right, it worked, thanx..
But I wouldn't pretend to understand, why :).
What is the different in those 2 ways of running SQL? Will smth. be checked by saving an SQL as a query, that not checked by running it from the DB2 control center?
Rgds,
Yuriy
you are right, it worked, thanx..
But I wouldn't pretend to understand, why :).
What is the different in those 2 ways of running SQL? Will smth. be checked by saving an SQL as a query, that not checked by running it from the DB2 control center?
Rgds,
Yuriy
Hi Yuriy,
DB2 has long had a few oddities that seem irritating, but when you understand the parser and rationale, it makes pretty good sense. Something similar to your issue is that column aliases don't propagate to the filter clause.
SELECT last || ', ' ||first as full_name FROM mytable WHERE full_name like 'A%';
That's illegal as the alias full_name isn't legal in the filter. But wrap it in an outer query and all is well.
SELECT * FROM (SELECT last || ', ' ||first as full_name FROM mytable) as t0
WHERE full_name like 'A%';
The rationale for not allowing a sort/fetch first as the last item in a view's SELECT statement is performance. On older versions of DB2 (and perhaps even current ones), DB2 would make two filter passes if the view filtered the results and the query calling the view had its own filter.
CREATE VIEW v1 AS SELECT * FROM cities WHERE name like 'A%';
SELECT * FROM v1 WHERE zip LIKE '9%';
In the example above, DB2 filters the results of the view (name like 'A%') and returns the results to the query where a second filtering pass is require to select the rows where zip like '9%'. It's more efficient to include both clauses in the view or the SELECT statement as both clauses are evaluated in the same pass.
Your situation is similar. DB2 is simply promoting better SQL, but providing a workaround to do exactly what you want.
Kent
DB2 has long had a few oddities that seem irritating, but when you understand the parser and rationale, it makes pretty good sense. Something similar to your issue is that column aliases don't propagate to the filter clause.
SELECT last || ', ' ||first as full_name FROM mytable WHERE full_name like 'A%';
That's illegal as the alias full_name isn't legal in the filter. But wrap it in an outer query and all is well.
SELECT * FROM (SELECT last || ', ' ||first as full_name FROM mytable) as t0
WHERE full_name like 'A%';
The rationale for not allowing a sort/fetch first as the last item in a view's SELECT statement is performance. On older versions of DB2 (and perhaps even current ones), DB2 would make two filter passes if the view filtered the results and the query calling the view had its own filter.
CREATE VIEW v1 AS SELECT * FROM cities WHERE name like 'A%';
SELECT * FROM v1 WHERE zip LIKE '9%';
In the example above, DB2 filters the results of the view (name like 'A%') and returns the results to the query where a second filtering pass is require to select the rows where zip like '9%'. It's more efficient to include both clauses in the view or the SELECT statement as both clauses are evaluated in the same pass.
Your situation is similar. DB2 is simply promoting better SQL, but providing a workaround to do exactly what you want.
Kent
Kent has the correct answer and deserves the credit. The short answer is simply:
Kent correctly gives a way to make it work, but the basic rule is not to assign ORDER BY for a VIEW. At least, not in the outer fullselect.
Tom
ExplanationVIEWs don't have ORDER BY (nor FETCH FIRST) clauses. An ORDER BY is for an INDEX, not a VIEW. Or an ORDER BY is for a run-time SELECT over a VIEW. VIEWs shouldn't have an order imposed on them; it contradicts choices of an order when a user wants a different sequence.
An ORDER BY or FETCH FIRST n ROWS ONLY is not allowed in:
the outer fullselect of a view
Kent correctly gives a way to make it work, but the basic rule is not to assign ORDER BY for a VIEW. At least, not in the outer fullselect.
Tom
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
-
Course of the Month6 days, 7 hours left to enrollMonitoring and Operating a Private Cloud with System Center 2012 R2 273 lessons
There are several easy ways around this. One is to sort/select using the OLAP tools (row_num() over ...) etc.
But the easiest is to just wrap the query with an outer query.
CREATE VIEW myview AS SELECT * FROM mytable ORDER BY 2 FETCH FIRST 1 ROWS ONLY;
Becomes
CREATE VIEW myview AS SELECT * FROM (SELECT * FROM mytable ORDER YB 2 FETCH FIRST 1 ROWS ONLY) as t0;
Good Luck,
Kent