## 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.

### 2 responses

12 09 2011

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

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.