FETCH FIRST 1 ROW ONLY Problem

Doug Greening

FETCH FIRST 1 ROW ONLY Problem
I've got the following SQL in a Cobol/DB2 V7 program that is trying to
retrieve 1 row only using the FETCH FIRST 1 ROW ONLY clause using a
singleton select instead of a cursor.

EXEC SQL SELECT F_ITM_NMBR, EFCTV_DT, CNCL_DT, F_SCHDL_AMNT,
VLDTY_INDCTR
INTO :DCLTFIDTL.F_ITM_NMBR,
:DCLTFIDTL.EFCTV_DT,
:DCLTFIDTL.CNCL_DT,
:DCLTFIDTL.F_SCHDL_AMNT,
:DCLTFIDTL.VLDTY_INDCTR
FROM TFIDTL
WHERE F_ITM_NMBR = :LAST_FEE AND
EFCTV_DT <= '2003-09-30' AND
VLDTY_INDCTR = 'Y'
ORDER BY 2 DESC
FETCH FIRST 1 ROW ONLY;

The Precompile step though gives me the following error

DSNH109I E DSNHAPLY LINE 320 COL 15 "ORDER BY" CLAUSE NOT PERMITTED

Which seems to indicate that :

Embedded SELECT statements cannot include ORDER BY, because the result
cannot be more than a single row.

I had thought this was exactly the kind of thing that the FETCH FIRST 1 ROW
was for. Any idea of what I'm doing wrong.

Thanks.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Phil Grainger

Re: FETCH FIRST 1 ROW ONLY Problem
(in response to Doug Greening)
Currently FETCH FIRST ROW is not supported for non-cursor SELECT statements

Now you'll probably ask me "Why?" - The answer is "Because it isn't"!!

This has been discussed in the past and is accepted (by most people) as an omission on IBMs part but that unfortunately doesn't change the fact that you can't do it!

Phil Grainger
Computer Associates
Product Manager, DB2
Tel: +44 (0)161 928 9334
Fax: +44 (0)161 941 3775
Mobile: +44 (0)7970 125 752
[login to unmask email]

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Greening, Doug HLTH:EX
Sent: Monday, December 22, 2003 6:42 PM
To: [login to unmask email]
Subject: FETCH FIRST 1 ROW ONLY Problem


I've got the following SQL in a Cobol/DB2 V7 program that is trying to retrieve 1 row only using the FETCH FIRST 1 ROW ONLY clause using a singleton select instead of a cursor.

EXEC SQL SELECT F_ITM_NMBR, EFCTV_DT, CNCL_DT, F_SCHDL_AMNT,
VLDTY_INDCTR
INTO :DCLTFIDTL.F_ITM_NMBR,
:DCLTFIDTL.EFCTV_DT,
:DCLTFIDTL.CNCL_DT,
:DCLTFIDTL.F_SCHDL_AMNT,
:DCLTFIDTL.VLDTY_INDCTR
FROM TFIDTL
WHERE F_ITM_NMBR = :LAST_FEE AND
EFCTV_DT <= '2003-09-30' AND
VLDTY_INDCTR = 'Y'
ORDER BY 2 DESC
FETCH FIRST 1 ROW ONLY;

The Precompile step though gives me the following error

DSNH109I E DSNHAPLY LINE 320 COL 15 "ORDER BY" CLAUSE NOT PERMITTED

Which seems to indicate that :

Embedded SELECT statements cannot include ORDER BY, because the result
cannot be more than a single row.

I had thought this was exactly the kind of thing that the FETCH FIRST 1 ROW was for. Any idea of what I'm doing wrong.

Thanks.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: FETCH FIRST 1 ROW ONLY Problem
(in response to Phil Grainger)
>Currently FETCH FIRST ROW is not supported for non-cursor SELECT statements
>Now you'll probably ask me "Why?" - The answer is "Because it isn't"!!
>This has been discussed in the past and is accepted (by most people) as an
omission on IBMs part but that unfortunately doesn't change the fact that
you can't do it!

The good news here, however, is that this *IS* fixed in DB2 v8.

I learned this in Akira Shibamiya's presentation* at the DB2 Tech conference
here in the US back in October.

Dave

*DB2 for z/OS V8 Application Performance Preview
at
ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/Z2
1.pdf
p 39 if I recall correctly.



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Brian Goldberg

Re: FETCH FIRST 1 ROW ONLY Problem
(in response to David Seibert)
Naturally, there is an exception. This is an example of existence checking:

INITIALIZE WS-CHAR-VAR.
EXEC SQL
SELECT
'Y'
INTO
:WS-CHAR-VAR
FROM
(your table here)
WHERE
(your selection criteria here)
FETCH FIRST 1 ROW ONLY
WITH UR
END-EXEC.



"Seibert, Dave"
<[login to unmask email] To: [login to unmask email]
PUWARE.COM> cc: (bcc: BRIAN GOLDBERG/QVC)
Ext: Subject: Re: FETCH FIRST 1 ROW ONLY Problem
Sent by: DB2 Data
Base Discussion
List
<[login to unmask email]
ORG>


12/22/2003 02:11
PM
Please respond to
DB2 Database
Discussion list
at IDUG






>Currently FETCH FIRST ROW is not supported for non-cursor SELECT
statements
>Now you'll probably ask me "Why?" - The answer is "Because it isn't"!!
>This has been discussed in the past and is accepted (by most people) as an
omission on IBMs part but that unfortunately doesn't change the fact that
you can't do it!

The good news here, however, is that this *IS* fixed in DB2 v8.

I learned this in Akira Shibamiya's presentation* at the DB2 Tech
conference
here in the US back in October.

Dave

*DB2 for z/OS V8 Application Performance Preview
at
ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/Z2

1.pdf
p 39 if I recall correctly.



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or
disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". If you will be out of the office, send the SET
DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins
can be reached at [login to unmask email] Find out the latest on
IDUG conferences at http://conferences.idug.org/index.cfm




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

David Seibert

Re: FETCH FIRST 1 ROW ONLY Problem
(in response to Brian Goldberg)
Brian,
I was confused by your reply until I more carefully read Phil's message.

"Fetch first 1 row"
_IS_ supported in singleton Select in v7.

I carelessly Phil's statement and assumed he was saying the Order By clause
isn't supported.
That is Doug's original question.

What is not supported in v7 and is supported in v8 is the _order by_ with
the fetch first 1 row clause in singleton select.

Sorry for clouding things up earlier.

Dave

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of [login to unmask email]
Sent: Monday, December 22, 2003 2:53 PM
To: [login to unmask email]
Subject: Re: FETCH FIRST 1 ROW ONLY Problem


Naturally, there is an exception. This is an example of existence checking:

INITIALIZE WS-CHAR-VAR.
EXEC SQL
SELECT
'Y'
INTO
:WS-CHAR-VAR
FROM
(your table here)
WHERE
(your selection criteria here)
FETCH FIRST 1 ROW ONLY
WITH UR
END-EXEC.




"Seibert, Dave"

<[login to unmask email] To:
[login to unmask email]
PUWARE.COM> cc: (bcc: BRIAN
GOLDBERG/QVC)
Ext: Subject: Re: FETCH FIRST 1
ROW ONLY Problem
Sent by: DB2 Data

Base Discussion

List

<[login to unmask email]

ORG>





12/22/2003 02:11

PM

Please respond to

DB2 Database

Discussion list

at IDUG









>Currently FETCH FIRST ROW is not supported for non-cursor SELECT
statements
>Now you'll probably ask me "Why?" - The answer is "Because it isn't"!!
>This has been discussed in the past and is accepted (by most people) as an
omission on IBMs part but that unfortunately doesn't change the fact that
you can't do it!

The good news here, however, is that this *IS* fixed in DB2 v8.

I learned this in Akira Shibamiya's presentation* at the DB2 Tech
conference
here in the US back in October.

Dave

*DB2 for z/OS V8 Application Performance Preview
at
ftp://ftp.software.ibm.com/software/db2storedprocedure/db2zos390/techdocs/Z2

1.pdf
p 39 if I recall correctly.


> Dave





The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". If you will be out of the office, send the SET DB2-L NO MAIL command to [login to unmask email] The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm