Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Location
    Edmonton, Canada
    Posts
    72

    Unanswered: DECODE With Comparison Operator

    Connected to:
    Oracle8i Enterprise Edition Release 8.1.7.0.0

    WHat I want to test for is if the ROWNUM is greater than X, just make it = X.

    Code:
    DECODE(ROWNUM, >10,10, ROWNUM)
    
    DECODE(ROWNUM>10,TRUE,10,ROWNUM)
    
    CASE WHEN ROWNUM > 10 THEN 10 ELSE ROWNUM END
    None of these work, any hot tips?

    Thanks

  2. #2
    Join Date
    May 2006
    Posts
    132
    I don't have an 8i database to test on, but this worked fine for 9i:

    Code:
    SQL> select tablespace_name, decode (sign(rownum - 5), +1, 5, rownum)
      2  from dba_data_files;
    
    TABLESPACE_NAME                DECODE(SIGN(ROWNUM-5),+1,5,ROWNUM)
    ------------------------------ ----------------------------------
    SYSTEM                                                          1
    UNDOTBS1                                                        2
    CWMLITE                                                         3
    DRSYS                                                           4
    EXAMPLE                                                         5
    INDX                                                            5
    ODM                                                             5
    TOOLS                                                           5
    USERS                                                           5
    XDB                                                             5
    
    10 rows selected.
    Code:
    SQL> select tablespace_name, case when rownum > 5 then 5 else rownum end
      2  from dba_data_files;
    
    TABLESPACE_NAME                CASEWHENROWNUM>5THEN5ELSEROWNUMEND
    ------------------------------ ----------------------------------
    SYSTEM                                                          1
    UNDOTBS1                                                        2
    CWMLITE                                                         3
    DRSYS                                                           4
    EXAMPLE                                                         5
    INDX                                                            5
    ODM                                                             5
    TOOLS                                                           5
    USERS                                                           5
    XDB                                                             5
    
    10 rows selected.
    Last edited by ebrian; 07-21-06 at 18:39.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    least(rownum,10)

  4. #4
    Join Date
    May 2006
    Posts
    132
    Good call andrewst, I forgot about that one.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •