We help IT Professionals succeed at work.
Get Started

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

inversojvo
inversojvo asked
on
2,593 Views
Last Modified: 2013-12-19
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
Comment
Watch Question
Data Warehouse / Database Architect
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 3 Answers and 5 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE