Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Error in executing Query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Tue May 25, 2010 10:05 pm    Post subject: Error in executing Query
Reply with quote

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
View user's profile Send private message

sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed May 26, 2010 1:03 am    Post subject:
Reply with quote

Rocky,

Cannot use FETCH or ORDER BY inside full-select of CTE.
If you use will get -199.

Sushanth
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Wed May 26, 2010 1:22 am    Post subject: Reply to: Error in executing Query
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed May 26, 2010 1:49 am    Post subject:
Reply with quote

Well, first i tried then i got experienced.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Wed May 26, 2010 7:43 pm    Post subject:
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed May 26, 2010 7:46 pm    Post subject:
Reply with quote

I am in DB2 V8.

Good to know it works in DB2 V9, Thank You GuyC for that information.



Sushanth
Back to top
View user's profile Send private message
rocky_balboa

New User


Joined: 24 Mar 2010
Posts: 61
Location: Cape of Good Hope

PostPosted: Wed May 26, 2010 9:48 pm    Post subject: Reply to: Error in executing Query
Reply with quote

Thanks Sushanth and GuyC for the information...actually I am also with DB2 V8 but was referring the V9 manual.....
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Getting error while opening a variabl... apandey1 COBOL Programming 5 Fri May 05, 2017 12:22 pm
No new posts IDC2902I Error tzeche ABENDS & Debugging 2 Tue Mar 28, 2017 11:39 am
No new posts Need help with below DB2 error ashek15 DB2 3 Sat Mar 18, 2017 5:56 am
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts FINDREP Syntax Error seahawk789 DFSORT/ICETOOL 4 Thu Mar 09, 2017 10:11 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us