|
View previous topic :: View next topic
|
| Author |
Message |
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
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 |
|
 |
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
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  |
|
| Back to top |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
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 |
|
|
Akatsukami
Global Moderator
Joined: 03 Oct 2009
Posts: 1788
Location: Bloomington, IL
|
|
|
|
| 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 |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
| 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 |
|
|
senthilssg
New User
Joined: 09 Dec 2005
Posts: 64
Location: USA
|
|
|
|
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 |
|
|
rockish
Active User
Joined: 05 Jun 2009
Posts: 185
Location: Planet Earth
|
|
|
|
| Thanks for your information Senthil. I will check the pre-compiler version.. |
|
| Back to top |
|
|
|
|
