Home » SQL & PL/SQL » SQL & PL/SQL » Regarding Rownum
Regarding Rownum [message #189527] Fri, 25 August 2006 00:46 Go to next message
vinnuk3
Messages: 3
Registered: August 2006
Location: Kuala Lumpur
Junior Member
Hi All,

I have one query Regarding rownum,

select * from VWRETDIVCANCELREQ, With this query I got 58 rows.

When I execute followin query I got only 13 rows. is anything wrong?

SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
FROM VWRETDIVCANCELREQ
WHERE ROWNUM<=25
MINUS
SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
FROM VWRETDIVCANCELREQ
WHERE ROWNUM<0

For above query If I add ROWNUM Column in Select Clause I am getting 25 rows. But I don't want to add rownum column in Select Clause.

Please check, It's urgent..
Thanks in Advance

SQL> SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/
mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
2 FROM iss_VWRETDIVCANCELREQ
3 WHERE ROWNUM<=25
4 MINUS
5 SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/
mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
6 FROM iss_VWRETDIVCANCELREQ
7 WHERE ROWNUM<0;

STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---- ----------------- ---------- ----------------- ------------
LIVE DC030003004000001 20/12/2002 DV020003004000001 PRU4
LIVE DC030003004000002 20/12/2002 DV020003004000001 PRU4
LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000002 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000003 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000004 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000006 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000008 15/12/2004 DV040300300000002 PRU3
LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
LIVE DC050300600000010 23/08/2005 DV050300600000002 PRU6

STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---- ----------------- ---------- ----------------- ------------
LIVE DC050300600000011 23/08/2005 DV050300600000002 PRU6
LIVE DC050300600000013 23/08/2005 DV050300600000002 PRU6

13 rows selected.

SQL> SELECT ROWNUM,DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DAT
E ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
2 FROM iss_VWRETDIVCANCELREQ
3 WHERE ROWNUM<=25
4 MINUS
5 SELECT ROWNUM,DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DAT
E ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
6 FROM iss_VWRETDIVCANCELREQ
7 WHERE ROWNUM<0;

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
1 LIVE DC030003004000001 20/12/2002 DV020003004000001 PRU4
2 LIVE DC030003004000002 20/12/2002 DV020003004000001 PRU4
3 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
4 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
5 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
6 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
7 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
8 LIVE DC050300300000002 15/12/2004 DV040300300000002 PRU3
9 LIVE DC050300300000003 15/12/2004 DV040300300000002 PRU3
10 LIVE DC050300300000006 15/12/2004 DV040300300000002 PRU3
11 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
12 LIVE DC050300300000004 15/12/2004 DV040300300000002 PRU3
13 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
14 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
15 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
16 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
17 LIVE DC050300300000008 15/12/2004 DV040300300000002 PRU3
18 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
19 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
20 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
21 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
22 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
23 LIVE DC050300600000010 23/08/2005 DV050300600000002 PRU6
24 LIVE DC050300600000011 23/08/2005 DV050300600000002 PRU6
25 LIVE DC050300600000013 23/08/2005 DV050300600000002 PRU6

25 rows selected.

[Updated on: Fri, 25 August 2006 01:24]

Report message to a moderator

Re: Regarding Rownum [message #189542 is a reply to message #189527] Fri, 25 August 2006 01:20 Go to previous messageGo to next message
gold_oracl
Messages: 129
Registered: July 2006
Location: Westborough, MA
Senior Member
Can you post your sample result. because here i'm getting correct result.
check below
SQL> SELECT * FROM TAB1;

         A B                   C
---------- ---------- ----------
         1 A1                 10
         1 B1                 10
         1 C1                 10
         2 A2
         2 B2

SQL> SELECT A, B, DECODE(C,NULL,30,C) V_VAL FROM TAB1 WHERE ROWNUM<5;

         A B               V_VAL
---------- ---------- ----------
         1 A1                 10
         1 B1                 10
         1 C1                 10
         2 A2                 30

SQL> SELECT A, B, DECODE(C,NULL,30,C) V_VAL FROM TAB1 WHERE ROWNUM<5 MINUS SELECT A, B, DECODE(C,NULL,30,C) V_VAL FROM TAB1 WHERE ROWNUM<0;

         A B               V_VAL
---------- ---------- ----------
         1 A1                 10
         1 B1                 10
         1 C1                 10
         2 A2                 30

SQL> 


Thanks,
Thangam
Re: Regarding Rownum [message #189545 is a reply to message #189527] Fri, 25 August 2006 01:26 Go to previous messageGo to next message
vinnuk3
Messages: 3
Registered: August 2006
Location: Kuala Lumpur
Junior Member
SQL> SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/
mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
2 FROM iss_VWRETDIVCANCELREQ
3 WHERE ROWNUM<=25
4 MINUS
5 SELECT DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DATE ,'dd/
mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
6 FROM iss_VWRETDIVCANCELREQ
7 WHERE ROWNUM<0;

STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---- ----------------- ---------- ----------------- ------------
LIVE DC030003004000001 20/12/2002 DV020003004000001 PRU4
LIVE DC030003004000002 20/12/2002 DV020003004000001 PRU4
LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000002 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000003 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000004 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000006 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
LIVE DC050300300000008 15/12/2004 DV040300300000002 PRU3
LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
LIVE DC050300600000010 23/08/2005 DV050300600000002 PRU6

STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---- ----------------- ---------- ----------------- ------------
LIVE DC050300600000011 23/08/2005 DV050300600000002 PRU6
LIVE DC050300600000013 23/08/2005 DV050300600000002 PRU6

13 rows selected.

SQL> SELECT ROWNUM,DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DAT
E ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
2 FROM iss_VWRETDIVCANCELREQ
3 WHERE ROWNUM<=25
4 MINUS
5 SELECT ROWNUM,DECODE(status,NULL,'LIVE',status) AS STATUS,DIV_CANCEL_REF, TO_CHAR( DECLARE_DAT
E ,'dd/mm/yyyy') AS DECLARE_DATE,DIV_REF,SCHEME
6 FROM iss_VWRETDIVCANCELREQ
7 WHERE ROWNUM<0;

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
1 LIVE DC030003004000001 20/12/2002 DV020003004000001 PRU4
2 LIVE DC030003004000002 20/12/2002 DV020003004000001 PRU4
3 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
4 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
5 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
6 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
7 LIVE DC050300300000001 15/12/2004 DV040300300000002 PRU3
8 LIVE DC050300300000002 15/12/2004 DV040300300000002 PRU3
9 LIVE DC050300300000003 15/12/2004 DV040300300000002 PRU3
10 LIVE DC050300300000006 15/12/2004 DV040300300000002 PRU3
11 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
12 LIVE DC050300300000004 15/12/2004 DV040300300000002 PRU3
13 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
14 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
15 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
16 LIVE DC050300300000007 15/12/2004 DV040300300000002 PRU3
17 LIVE DC050300300000008 15/12/2004 DV040300300000002 PRU3
18 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
19 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
20 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
21 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5
22 LIVE DC050300500000009 23/08/2005 DV050300500000001 PRU5

ROWNUM STAT DIV_CANCEL_REF DECLARE_DA DIV_REF SCHEME
---------- ---- ----------------- ---------- ----------------- ------------
23 LIVE DC050300600000010 23/08/2005 DV050300600000002 PRU6
24 LIVE DC050300600000011 23/08/2005 DV050300600000002 PRU6
25 LIVE DC050300600000013 23/08/2005 DV050300600000002 PRU6

25 rows selected.
Re: Regarding Rownum [message #189550 is a reply to message #189545] Fri, 25 August 2006 01:40 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
The minus removes duplicate rows. Your rownum column makes the rows unique, whence the 25 rows instead of 13.
You can use the sqlplus formatting command to not display the column:
column rownum noprint
Re: Regarding Rownum [message #189575 is a reply to message #189527] Fri, 25 August 2006 02:58 Go to previous messageGo to next message
vinnuk3
Messages: 3
Registered: August 2006
Location: Kuala Lumpur
Junior Member
Frank,
Thanks for u r suggestion. I just want to know why is this difference.
Re: Regarding Rownum [message #189613 is a reply to message #189550] Fri, 25 August 2006 04:16 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
vinnuk3 wrote on Fri, 25 August 2006 09:58

I just want to know why is this difference.


Frank wrote on Fri, 25 August 2006 08:40

The minus removes duplicate rows. Your rownum column makes the rows unique, whence the 25 rows instead of 13.


Previous Topic: what is the username/password for oracle 9i. like scott/tiger for personal oracle for win95
Next Topic: "dynamic" cursor
Goto Forum:
  


Current Time: Sat Dec 10 18:21:01 CST 2016

Total time taken to generate the page: 0.07610 seconds