MISCELLANEOUS FUNCTIONS

MISCELLANEOUS FUNCTIONS

Ø  Uid

Ø  User

Ø  Vsize

Ø  Rank

Ø  Dense_rank

a) UID

     This will returns the integer value corresponding to the user currently logged in.

     Ex:

          SQL> select uid from dual;

       UID

----------

       319

b) USER

     This will returns the login’s user name.

     Ex:

           SQL> select user from dual;

USER

----------------

SAKETH

c) VSIZE

     This will returns the number of bytes in the expression.

     Ex:

          SQL> select vsize(123), vsize('computer'), vsize('12-jan-90') from dual;

VSIZE(123) VSIZE('COMPUTER') VSIZE('12-JAN-90')

-------------  -----------------------  ----------------------

         3                         8                                  9

d) RANK

     This will give the non-sequential ranking.

     Ex:

          SQL> select rownum,sal from (select sal from emp order by sal desc);

    ROWNUM    SAL

    ---------- ----------

         1       5000

         2       3000

         3       3000

         4       2975

         5       2850

         6       2450

         7       1600

         8       1500

         9       1300

        10       1250

        11       1250

        12       1100

        13       1000

        14        950

        15        800

     SQL> select rank(2975) within group(order by sal desc) from emp;

RANK(2975)WITHINGROUP(ORDERBYSALDESC)

---------------------------------------------------------

                                    4

d) DENSE_RANK

     This will give the sequential ranking.

    

Ex:

     SQL> select dense_rank(2975) within group(order by sal desc) from emp;

DENSE_RANK(2975)WITHINGROUP(ORDERBYSALDESC)

-----------------------------------------------------------------