Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


Tip of the Day
Language: Enterprise
Expertise: Intermediate
Jun 26, 2001

Using ROWNUM To Retrieve


It is often necessary to get a list of values from multiple rows. Generally, this is done using the cursor, which tends to make the task more complicated.

I tried to use ROWNUM pseudocolumn as:
 
 SELECT GEN_CODE
  INTO :temp[i]
	FROM  GEN_TABLE
	WHERE  ROWNUM = i;

It works only for i=1!, because selecting only one row defaulted ROWNUM = 1 !. In order to select one row at a time, use nested select as shown here:
 
i=0
DO
  i =i+1
  SELECT GEN_CODE
  INTO :temp[i]
FROM   (SELECT GEN_CODE, ROWNUM rn
	FROM   GEN_TABLE)
	  WHERE rn =:i;
LOOP UNTIL SQLCA.SQLCode = 100

Note: It is important to use aliase rn, because ROWNUM is gets overriden by the outer select.
Lev Romanov
 
Comment and Contribute

 

 

 

 

 


(Maximum characters: 1200). You have 1200 characters left.

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date