|
View previous topic :: View next topic
|
| Author |
Message |
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
Hi
I am getting error while executing the following query in QMF:
WITH TEST_TAB(MLB,TS) AS
(SELECT T1.AC_NR,T1.CUS FROM AAAAA.TAB AS T1 ORDER BY
T1.CUS DESC FETCH FIRST 2 ROWS ONLY)
SELECT MLB,MIN(TS) FROM TEST_TAB GROUP BY MLB ORDER BY MLB;
Following msg I am getting
------------------------------------------
SQL error at or before ORDER
Some common errors that produce this message are:
1. Use of the word DISTINCT more than once in a subquery.
2. Missing or inappropriate keyword or punctuation.
3. An unparenthesized subquery after select.
The line below lists one or more symbols that might have been correct,
based on the context so far, though they might not all work with the
whole query.
The list of alternate symbols are only suggestions. Some of the symbols
might not be legal statements for the database manager being used. Those
symbols might be correct for statements sent to other database managemen
systems.
) UNION EXCEPT
SQL Code: -199
---------------------------------------------
How can I resolve this?
Thanks |
|
| Back to top |
|
 |
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
Rocky,
Cannot use FETCH or ORDER BY inside full-select of CTE.
If you use will get -199.
Sushanth |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
Thanks!!!..
I had this inkling....because when I executed various forms of this query without an ORDER BY they were running fine....but I was not sure....
By the way...did you come to know of this by experience( like me).... or through a manual..... I tried searching the DB2 9 SQL manual for z/OS but could not find it... |
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
| Well, first i tried then i got experienced. |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
order by fetch first in a full select of the CTE works fine for me : DB2 V9 NFM
| Code: |
with biggest(dbid,name) as
(select dbid, name from sysibm.sysdatabase
order by dbid desc fetch first 2 rows only)
select B.dbid, B.name,count(*) from biggest B, sysibm.systablespace A
where b.dbid = a.dbid
group by B.dbid, B.name |
|
|
| Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
I am in DB2 V8.
Good to know it works in DB2 V9, Thank You GuyC for that information.
Sushanth |
|
| Back to top |
|
|
rocky_balboa
New User
Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope
|
|
|
|
| Thanks Sushanth and GuyC for the information...actually I am also with DB2 V8 but was referring the V9 manual..... |
|
| Back to top |
|
|
|
|
