Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

How to fetch first record only with out CURSOR

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions
View previous topic :: :: View next topic  
Author Message
dp33770

New User


Joined: 04 Jul 2007
Posts: 92
Location: Hyderabad

PostPosted: Fri Sep 19, 2008 2:37 pm    Post subject: How to fetch first record only with out CURSOR
Reply with quote

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
View user's profile Send private message

Aaru

Senior Member


Joined: 03 Jul 2007
Posts: 1288
Location: Chennai, India

PostPosted: Fri Sep 19, 2008 2:43 pm    Post subject: Reply to: Interviw Question
Reply with quote

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
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Fri Sep 19, 2008 2:43 pm    Post subject:
Reply with quote

2. Without "ORDER By" clause this answer is bit garbage.
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Sep 19, 2008 3:31 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Sep 19, 2008 3:44 pm    Post subject:
Reply with quote

Quote:
FIRST 3 ROWS ONLY


Is it

Code:
  FETCH FIRST  ROW ONLY
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Sep 19, 2008 3:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Fri Sep 19, 2008 4:04 pm    Post subject:
Reply with quote

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. icon_lol.gif

Thanks,
Arun
Back to top
View user's profile Send private message
revel

Active User


Joined: 05 Apr 2005
Posts: 135
Location: Bangalore/Chennai-INDIA

PostPosted: Fri Sep 19, 2008 4:22 pm    Post subject:
Reply with quote

Hello,

If it so,

Quote:
DB2 is really intelligent. It can understand ordinary english


How many record will fetch for above query icon_wink.gif
Back to top
View user's profile Send private message
Arun Raj

Moderator


Joined: 17 Oct 2006
Posts: 2275
Location: @my desk

PostPosted: Fri Sep 19, 2008 4:25 pm    Post subject:
Reply with quote

Quote:
How many record will fetch for above query

What do you think? Try and see for yourself.

Thanks,
Arun
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Sep 19, 2008 4:46 pm    Post subject: Reply to: Interviw Question
Reply with quote

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
View user's profile Send private message
ksk

Active User


Joined: 08 Jun 2006
Posts: 356
Location: New York

PostPosted: Fri Sep 19, 2008 4:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
dp33770

New User


Joined: 04 Jul 2007
Posts: 92
Location: Hyderabad

PostPosted: Fri Sep 19, 2008 8:12 pm    Post subject: Reply to: How to fetch first record only with out CURSOR
Reply with quote

Thanks A lot for such wonderful explanation !!!!
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Sat Sep 20, 2008 2:24 am    Post subject:
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> Mainframe Interview Questions All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Testing rerad cursor for status with ... John F Dutcher DB2 8 Fri May 19, 2017 9:35 pm
No new posts Adding big TEXT lines to each record ... bshkris SYNCSORT 4 Sat May 06, 2017 1:40 am
This topic is locked: you cannot edit posts or make replies. Merge two files and update Trailer re... Yashashri JCL & VSAM 1 Thu May 04, 2017 12:54 pm
No new posts Sort Large record length cmsmoon DFSORT/ICETOOL 14 Tue Apr 11, 2017 5:49 pm
No new posts Format record to remove Leading zeroes Learncoholic DFSORT/ICETOOL 14 Wed Apr 05, 2017 2:43 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us