CodeSnip: How to Select the Records from a Table Following a Pattern without Using ROWNUM
page 3 of 4
by Deepankar Sarangi
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 19721/ 27


Listing 1

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


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

View Entire Article

User Comments

No comments posted yet.

Product Spotlight
Product Spotlight 

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

©Copyright 1998-2024  |  Page Processed at 2024-04-15 7:00:46 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search