View previous topic :: View next topic
|
Author |
Message |
sri_mf
Active User
Joined: 31 Aug 2006 Posts: 218 Location: India
|
|
|
|
Hi,
I have a Query.It is retrieving many records.But i am taking the first record only using FETCH FIRST 1 ROW ONLY.Will this cause -811 abend if i do not use Cursor. |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
|
|
If you provide FETCH FIRST 1 ROW ONLY, you won't get -811 abend even w/o using cursor. Refer the following information.
Quote: |
Prior to V7, when you coded a SELECT INTO in your application, you had to insure that only one row was return or you would end up with a -811 instead of an answer. Then in V7, a new clause was introduced for the SELECT statement. You could now code a FETCH FIRST n ROWS ONLY clause on the SELECT and control the number of rows returned. In the case of out -811, FETCH FIRST 1 ROW ONLY would satisfy the requirement of a SELECT INTO only allowing one row returned. But new feature did come with some restrictions.
Well, in Version 8, one of those restrictions was removed. In V8, you can code an ORDER BY and FETCH FIRST on the SELECT INTO statement. When combined on the same statement, an answer set is created, sorted, and then the first n rows are retuned to the application. If this is a SELECT INTO, the n would be set to 1.
SELECT FROM INTO :host_variable WHERE ORDER BY FETCH FIRST 1 ROW ONLY
So now if you are simply looking for the highest, lowest, first, or last row in an answer set and you only what to return that one row from the answer set to your application without having to code a cursor, it can be easily accomplished.
|
|
|
Back to top |
|
|
birdy K
New User
Joined: 05 Mar 2008 Posts: 72 Location: chennai
|
|
|
|
Hi KSK,
I know about Fetch first 1 row. U have given as we can use order by clause in V8. I am using V8. I tried with order by clause which is giving error. Please confirm whether we can use order by clause. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
KSK,
what is the source of your quote? |
|
Back to top |
|
|
ksk
Active User
Joined: 08 Jun 2006 Posts: 355 Location: New York
|
|
Back to top |
|
|
sri_mf
Active User
Joined: 31 Aug 2006 Posts: 218 Location: India
|
|
|
|
Thanks to every one for all the replies. |
|
Back to top |
|
|
|