WHERE MOD(ROWNUM,100) = 0

30 06 2011

June 30, 2011

I saw an interesting search keyword the other day that generated several hits on my blog articles.  The search keyword was simply:

WHERE MOD(ROWNUM,100) = 0

Just what is interesting about the above?  Had someone seen that syntax and wondered how it worked, or was someone trying to use that syntax and found that it did not work quite as expected?

The MOD function returns the remainder value when dividing the number (ROWNUM in this case) by the specified denominator value (100 in this case).  The result of the MOD function where 100 is specified as the second parameter, with the sequential numbers 1-201 fed in as the first parameter, will appear as follows:

SELECT
  MOD(ROWNUM,100) C1
FROM
  DUAL
CONNECT BY
  LEVEL<=201;

C1
--
 1
 2
 3
 4
 5
 6
...
98
99
 0
 1
 2
 3
 4
...
98
99
 0
 1

As shown above, there is a sequence of 1-99, and then repeating  sequences of 0-99.  Note, however, that it appears the person performing the search is intending to place the MOD function in the WHERE clause, likely to retrieve every 100th row in the resultset.  The problem, of course, is this approach does not work with the ROWNUM pseudo-column.

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(ROWNUM,100) = 0;

no rows selected 

However, if we create a table and populate the table with the same 1000 rows that would be produced by the inline view found in the above SQL statement:

DROP TABLE T1 PURGE;

CREATE TABLE T1 AS
SELECT
  ROWNUM C1
FROM
  DUAL
CONNECT BY
  LEVEL<=1000;  

Then, select from that table, switching out the ROWNUM keyword with the C1 column found in the table T1:

SELECT
  *
FROM
  T1
WHERE
  MOD(C1,100) = 0;

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

We see that 10 rows were returned – every 100th row.  At this point, you might be wondering if the following will work:

SELECT
  *
FROM
  (SELECT
     ROWNUM C1
   FROM
     DUAL
   CONNECT BY
     LEVEL<=1000)
WHERE
  MOD(C1,100) = 0; 

Here is the output of the above SQL statement:

    C1
------
   100
   200
   300
   400
   500
   600
   700
   800
   900
  1000

10 rows selected. 

So, the query works as expected when we use the alias of the ROWNUM column from the inline view when the MOD function appears in the WHERE clause, but the query does not work as (probably) intended when the ROWNUM pseudo-column is used directly in the MOD function in the WHERE clause.  Why?  Think about when the ROWNUM value is assigned – not until after the WHERE clause is applied.  See the comments attached in this related blog article.


Actions

Information

2 responses

12 09 2011
ufuk

hello ,
i need help pls help me
i ve a view there are tables in it but i ve no any unique id in it so i need an unique id for it so i added rowid but after that i cant get any result from my query

12 09 2011
Charles Hooper

This blog site is not the ideal place to ask questions like the above (try the OTN forums, UseNet, or AskTom sites). It is very difficult to understand what you are describing (the instant messaging abbreviations add to the difficulty).

Let me see if I can re-phrase your comment – please verify that the following is what you are experiencing:

I have a view that accesses several tables, however I cannot issue UPDATE or DELETE statements against the view because I am receiving one of the following error messages:
* ORA-01752: cannot delete from view without exactly one key-preserved table (see page 276 of the “Beginning Oracle SQL” book)
* ORA-01779: cannot modify a column which maps to a non key-preserved table (see page 276 of the “Beginning Oracle SQL” book)

I have not yet tried using Instead-Of-Triggers (http://download.oracle.com/docs/cd/E14072_01/appdev.112/e10472/triggers.htm#i1006376 ). I have tried using the ROWID pseudo column from one of the tables to provide a unique column, but now the query returns no rows. Here is my view definition:

CREATE VIEW MY_VIEW AS
SELECT
  ROWID,
  T1.C1,
  T2.C2
FROM
  T1,
  T2
WHERE
  T1.C1=T2.C1;

Is the above a close representation of what you are trying to do? Please show the exact view definition and what you are trying to do with the view.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s




Follow

Get every new post delivered to your Inbox.

Join 140 other followers

%d bloggers like this: