CodeSnip: How to Select the Records from a Table Following a Pattern without Using ROWNUM
 
Published: 14 Apr 2008
Abstract
In this article Deepankar discusses how to select records from an Oracle 9i table without using ROWNUM. Deepankar provides a detailed analysis of each SQL statement along with the screenshot of the final output.
by Deepankar Sarangi
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 20903/ 71

Introduction

This code snippet is an extension of my earlier code snip How to select the records from a table following a pattern. There I used ROWNUM to find the even/odd records from the emp table. Current code snip achieves the same functionality even without the use of ROWNUM. This method proves useful when it is asked to avoid the use of ROWNUM.

Requirements

Oracle 9i or above versions

Code

Listing 1

select * from emp where MOD(ASCII(SUBSTR(ROWID,LENGTH(ROWID))),2)=1;

Explanation

In one of my earlier code snips I used the ROWNUM to manipulate the record number mathematically in order to get the odd number of records as given in Listing 2.

Listing 2

select * from emp where (rowid,1) in (select rowid,mod(rownum,2) from emp);

But without using rownum it becomes difficult to manipulate the record number mathematically. In this situation, the approach mentioned in Listing 1 proves handy. The code uses a key word, like rownum, and many oracle functions, like mod(), ascii(), substr() and length(). Oracle points each record in a table through a unique pattern consisting of only alphabets called ROWID. In order to use ROWID's mathematically, we need to interpret the ROWID's of each row mathematically.

Interpretation can be understood from the snapshot beloweasily.

Figure 1

All functions.bmp

Let us dig deep into the query in Listing 1.

First the length() function captures the length of ROWID assigned for each record which returns the maximum character length for each ROWID.

Then use of the SUBSTR(ROWID,LENGTH(ROWID)) function can peak the right most alphabet of each ROWID which uniquely represents each record in the EMP table as shown by Figure 1.

Now by using ASCII(SUBSTR(ROWID,LENGTH(ROWID))) we can interpret each ROWID mathematically to manipulate in any useful form.

Finally, to extract the odd number of records we have to peak the ROWID that upon dividing by 2 leaves the reminder as 1.

That can be easily done by MOD(ASCII(SUBSTR(ROWID,LENGTH(ROWID))),2)=1.

Figure 2: Printing Odd numbered rows by using Listing 1

Odd rows.bmp

Conclusion

The above approach explains how the ROWID can be manipulated mathematically to get odd/even records from a table.



User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-04-21 8:53:08 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search