DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Decode question

  1. #1
    Join Date
    Mar 2002
    Posts
    17
    create table emp ( ename varchar2(20),
    empno varchar2(10),
    job varchar2(20),
    hiredate date,
    salary number (10,2),
    comm number (10,2));

    Then insert rows

    create view emp_view as (
    select ename, empno, job, hiredate,
    decode ( ename, user, sal, 'KING',sal, 0) as sal,
    decode ( ename, user, comm, 'KING', sal, 0) as comm
    from emp);

    I understand the basic decode( ename, 'kevin', MANAGER)
    as if ename equal kevin then replace with MANAGER, but I don't understand decode( ename, user, sal, 'KING',sal, 0) as sal,

    Please explain... thank you

  2. #2
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    1)
    decode ( ename,
    user, sal,
    'KING',sal,
    0) as sal,
    --------------------------------------------------------
    2)
    decode ( ename,
    user, comm,
    'KING', sal,
    0) as comm
    --------------------------------------------------------
    1) explain:
    if field ENAME = user (User fo current session > LOGON) then
    return value from field SAL
    else
    if field ENAME = 'KING' (as constant) then return value from field SAL
    for any others value from field ENAME return 0.

    2)
    if field ENAME = user (User fo current session > LOGON) then
    return value from field COMM (probably commitions)
    else
    if field ENAME = 'KING' (as constant) then return value from field SAL
    for any others value from field ENAME return 0.
    ----------------------------------------------------------------
    Note: USER is standart function, that return name of current user(session)

  3. #3
    Join Date
    Mar 2002
    Posts
    17
    Thanks for the quick reply, that makes sense.

Posting Permissions

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


Click Here to Expand Forum to Full Width