troubleshooting Question

DB2 - problem by creating view with select left join and order by..

Avatar of inversojvo
inversojvoFlag for Germany asked on
DB2
5 Comments3 Solutions2595 ViewsLast Modified:
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_TIERPARKLISTE l on t.TIERPARK_ID = l.TIERPARK_ID order by GUTSCHEINNR;

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_TIERPARKLISTE l on t.TIERPARK_ID = l.TIERPARK_ID order by GUTSCHEINNR;

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_TIERPARKLISTE l on t.TIERPARK_ID = l.TIERPARK_ID order by GUTSCHEINNR
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
ASKER CERTIFIED SOLUTION
Kent Olsen
Data Warehouse / Database Architect

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 3 Answers and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 3 Answers and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros