Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
DB2 v8 mode - Order By clause not permitted

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

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Dec 31, 2009 10:08 pm    Post subject: DB2 v8 mode - Order By clause not permitted
Reply with quote

Hello,

I am new to my work place. I was trying to execute a singleton select query like the following from a COBOL program,

Code:
SELECT a,b,c INTO :H, :H, :H
FROM Table1
WHERE col1 = :H
ORDER BY col2 DESC
FETCH FIRST ROW ONLY;


But, I get a compilation error as below,
Code:
DSNH109I E     DSNHAPLY LINE 360 COL 15  "ORDER BY" CLAUSE NOT PERMITTED


This syntax use to work well in my previous workplaces and I think usage of SELECT.. INTO.. ORDER BY is possible in DB2 v8 NFM.

I would now like to check the mode of DB2 v8 that my shop is in. Is there a command or a place where I can check it myself ?
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1777
Location: Bloomington, IL

PostPosted: Thu Dec 31, 2009 10:20 pm    Post subject: Re: DB2 v8 mode
Reply with quote

rockish wrote:
I think usage of SELECT.. INTO.. ORDER BY is possible in DB2 v8 NFM.

You think incorrectly. DB2 UDB for z/OS v8 Messages quite definitely states:
Quote:
A SELECT INTO statement cannot include ORDER BY, because the result cannot be more than a single row.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Dec 31, 2009 10:25 pm    Post subject:
Reply with quote

Thanks for your reply.

Well, Yeah.. I indeed checked the messages in DB2 v8 and DB2 v9 guides as well. And I got the message you have quoted.

But I still remember like, I have written such queries in Cobol modules in my previous projects and they use to work.

Am in a totally confused state as now icon_sad.gif
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Thu Dec 31, 2009 10:46 pm    Post subject:
Reply with quote

Well, I was doing a lit bit more of googling and found the following,

As per the red book titled 'DB2 UDB for z/OS Version 8: Everything You Ever Wanted to Know, ... and More' , in section 4.26, Page number 385
Quote:
The SELECT INTO statement must produce a result that contains a single row; else an
SQLCODE -811 is returned. Prior to DB2 V7, there was no way of ensuring that only a single
row could be returned when using a SELECT INTO statement. You had to use a cursor, and
the program itself would have to read in only the first row that matched the predicates and
throw away all other rows. With the addition of the FETCH FIRST n ROWS ONLY clause in
V7, this situation can be avoided. V7 allows you to code the FETCH FIRST ROWS ONLY
clause on a SELECT INTO statement to indicate that only one row is to be returned to the
program, even if multiple rows match the WHERE criteria. However, you could not specify the
ORDER BY clause to affect which row was returned.
With DB2 for z/OS Version 8, you can now specify the ORDER BY clause as well. When you
use both the FETCH FIRST 1 ROW ONLY and ORDER BY clauses, the result set is retrieved
and ordered first, and then the first row is returned. For example, using the sample employee
table, for all employees with a salary of more than $40000, put the salary of the employee
who has been employed the longest in host variable :HV1. This query can be coded as
shown in Example 4-8.
Code:
Example 4-8 SELECT INTO using ORDER BY
SELECT SALARY
FROM DSN8810.EMP
INTO :HV1
WHERE SALARY > 40000
ORDER BY HIREDATE FETCH FIRST ROW ONLY;

This functionality is only available in New-function mode.


With more thanks to google, I managed to find out my DB2 version and mode using a DB2 command,

Code:
DSN7100I  _DSN2 DSN7GCMD                                           
*** BEGIN DISPLAY OF GROUP(........) GROUP LEVEL(...) MODE(N)       
                  PROTOCOL LEVEL(2)  GROUP ATTACH NAME(....)       
--------------------------------------------------------------------
DB2                                    DB2 SYSTEM    IRLM           
MEMBER   ID  SUBSYS CMDPREF   STATUS   LVL NAME      SUBSYS IRLMPROC
-------- --- ----   --------  -------- --- --------  ----   --------
........   0 DSN2   _DSN2     ACTIVE   810 DCU9      RLM2   RLM2PROC
--------------------------------------------------------------------
*** END DISPLAY OF GROUP(........)                                 
DSN9022I  _DSN2 DSN7GCMD 'DISPLAY GROUP ' NORMAL COMPLETION         
***                                                                 

And it clearly uses Db2 v8 in NFM

Now, I totally dont understand why my compilation fails !!!!!

Any clues anybody ???
Back to top
View user's profile Send private message
Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1777
Location: Bloomington, IL

PostPosted: Fri Jan 01, 2010 12:21 am    Post subject:
Reply with quote

Playing with this a bit, it seems to be an issue with the particular precompiler. I can only suggest that you contact the DB2 mavens at IBM, and follow their instructions implicitly.
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Fri Jan 01, 2010 12:25 am    Post subject:
Reply with quote

hehe.. yeah.. after all that is why I posted this in the forum so that someone could help me with their suggestions :-)
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Mon Jan 04, 2010 7:31 am    Post subject:
Reply with quote

Please check with your DBA which version of DB2 precompiler is currently using in your shop .

This can be check by looking the version number in the job output of pre compilation JCL ie compilation listing

Note: -DIS GROUP command says what is the status of DB2 catalog with respect to DB2 migration .
If your shop use DB2 v7 pre compiler in DB2 v8 also , you may get this error .

Thanks
Senthil
Back to top
View user's profile Send private message
rockish

Active User


Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth

PostPosted: Wed Jan 06, 2010 1:44 am    Post subject:
Reply with quote

Thanks for your information Senthil. I will check the pre-compiler version..
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 Sorting on text - but in non-alphabet... Roy Ware SYNCSORT 5 Wed Aug 23, 2017 9:15 pm
No new posts Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts What is the command to check MODE of ... rohanthengal CLIST & REXX 7 Fri Nov 18, 2016 1:48 pm
No new posts Need help on SQL Dynamic WHERE Clause subratarec DB2 12 Sat Jul 16, 2016 3:11 pm
No new posts SQL Order By related question Joseph K Thomas DB2 8 Fri Mar 18, 2016 12:53 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us