|
View previous topic :: View next topic
|
| Author |
Message |
dp33770
New User
Joined: 04 Jul 2007
Posts: 91
Location: Hyderabad
|
|
|
|
Can anyone answer for the below question
1. Suppose a cursor retrieves 100 records and we ar in the 60th record of the Fetch cursor. How to go back to 1oth record.
Ans: I was not able to answer this qustion.
2. Suppose we have a select stmt which retrieved more than one record. We have not declared any cursor. How to fetch first record only.
Ans: I have answered FETCH FIRST RECORD ONLY. Is it correct |
|
| Back to top |
|
 |
Aaru
Senior Member
Joined: 03 Jul 2007
Posts: 1287
Location: Chennai, India
|
|
|
|
Deep,
| Quote: |
| 2. Suppose we have a select stmt which retrieved more than one record. We have not declared any cursor. How to fetch first record only. |
AFAIK, this would lead to -811 (SQLCODE). |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
| 2. Without "ORDER By" clause this answer is bit garbage. |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Hi deep,
| Quote: |
Suppose a cursor retrieves 100 records and we ar in the 60th record of the Fetch cursor. How to go back to 1oth record.
Ans: I was not able to answer this qustion. |
If they are using DB2 V9, Then there is verb called
| Code: |
EXEC SQL
FETCH RELATIVE -50 C1
INTO :ws-storage variable
END-EXEC |
Where -50 represents a integer(referance number) from current position, since current position is at 60th record; -50 refers 10th position
| Quote: |
Suppose we have a select stmt which retrieved more than one record. We have not declared any cursor. How to fetch first record only.
Ans: I have answered FETCH FIRST RECORD ONLY. Is it correct |
The answer is right, but there is typo seems,
The synatx is
FETCH FIRST 3 ROWS ONLY
In case if you are not using above query, It will fetches more than 1 records so it will throw -811 sqlcode as Aaru said |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
Is it
| Code: |
| FETCH FIRST ROW ONLY |
|
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
prasanth,
You should mention integer number
| Code: |
| FECH FIRST ROW ONLY |
In above code, How come it will recognize how many rows need to be fetched. FIRST represent only position means starting from 1st rows.
So the syntax is
| Code: |
select * from org where deptname = 'Sales' fetch first 1 rows
only |
|
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| In above code, How come it will recognize how many rows need to be fetched |
| Code: |
| FETCH FIRST ROW ONLY |
DB2 is really intelligent. It can understand ordinary english. 
Thanks,
Arun |
|
| Back to top |
|
|
revel
Active User
Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA
|
|
|
|
Hello,
If it so,
| Quote: |
| DB2 is really intelligent. It can understand ordinary english |
How many record will fetch for above query  |
|
| Back to top |
|
|
Arun Raj
Moderator
Joined: 17 Oct 2006
Posts: 2481
Location: @my desk
|
|
|
|
| Quote: |
| How many record will fetch for above query |
What do you think? Try and see for yourself.
Thanks,
Arun |
|
| Back to top |
|
|
Prasanthhere
Active User
Joined: 03 Aug 2005
Posts: 306
|
|
|
|
| Quote: |
| In above code, How come it will recognize how many rows need to be fetched. FIRST represent only position means starting from 1st rows |
Revel,
So you mean to say
| Code: |
| Select * from tablename fetch first row only; |
Won't work and will give an SQL error . |
|
| Back to top |
|
|
ksk
Active User
.jpg)
Joined: 08 Jun 2006
Posts: 355
Location: New York
|
|
|
|
| Quote: |
1. Suppose a cursor retrieves 100 records and we ar in the 60th record of the Fetch cursor. How to go back to 1oth record.
|
Using scrollable cursors you can acheive this. For this you need to have DB2 V7. See the below explanation.
Scrollable cursors
Cursors may be declared as being scrollable or not. The scrollability indicates the direction in which a cursor can move. A non-scrollable cursor is also known as forward-only. Each row can be fetched at most once, and the cursor automatically moves to the immediately following row. A fetch operation after the last row has been retrieved positions the cursor after the last row and returns SQLSTATE 02000 (SQLCODE +100).
A scrollable cursor can be positioned anywhere in the result set using the FETCH SQL statement. The keyword SCROLL must be specified when declaring the cursor. The default is NO SCROLL, although different language bindings like JDBC may apply different default.
DECLARE cursor_name sensitivity SCROLL CURSOR FOR SELECT ... FROM ...
The target position for a scrollable cursor can be specified relative to the current cursor position or absolute from the beginning of the result set.
FETCH [ NEXT | PRIOR | FIRST | LAST ] FROM cursor_name
FETCH ABSOLUTE n FROM cursor_name
FETCH RELATIVE n FROM cursor_name
Scrollable cursors can potentially access the same row in the result set multiple times. Thus, data modifications (insert, update, delete operations) from other transactions could have an impact on the result set. A cursor can be SENSITIVE or INSENSITIVE to such data modifications. A sensitive cursor picks up data modifications impacting the result set of the cursor, and an insensitive cursor does not. Additionally, a cursor may be ASENSITIVE, in which case the DBMS tries to apply sensitivity as much as possible. |
|
| Back to top |
|
|
dp33770
New User
Joined: 04 Jul 2007
Posts: 91
Location: Hyderabad
|
|
|
|
| Thanks A lot for such wonderful explanation !!!! |
|
| Back to top |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6250
Location: Mumbai, India
|
|
|
|
Hello,
| revel wrote: |
| How many record will fetch for above query |
This
| Code: |
| FETCH FIRST ROW ONLY |
and
| Code: |
| FETCH FIRST 1 ROWS ONLY |
and
| Code: |
| FETCH FIRST 3 ROWS ONLY |
all are correct but without a ORDER BY clause results will be inconsistent.
In this
| Quote: |
| represent only position means starting from 1st rows. |
you are conceptually right. Using above queris You just arbitrarily selected a row. It could be different the next time you do it, or it could be the same for the life of the table. Again, this is a complete waste of a query without an ORDER BY clause. |
|
| Back to top |
|
|
|
|
