Forum Stats

  • 3,734,027 Users
  • 2,246,861 Discussions
  • 7,857,001 Comments

Discussions

Finding value for a specific condition

536807
536807 Member Posts: 115
edited July 2008 in SQL & PL/SQL
Hi All,

I have a several Employee Numbers in my Database Table. Each Employee is having different set of Profile Numbers.

For eg. the Table Emp_Profile contains the following

Emp No ProfileNo Salary Red_Amount
1000 11 1200 430
1000 12 100 55
1000 13 120 7
1000 14 430 8
1000 15 300 40
1000 43 200 20
1000 56 100 232
1010 11 1000 300
1010 14 110 700
1010 19 200 500
1010 12 410 545
1010 13 300 54


If the Employee contains 19,45,67,89,99,24 as ProfileNo then For Each Employee reduce the Salary value for 11 ProfileNo by the Corresponding Red_Amount

Ie the Output Should be

For
EmpNo Salary
1000 1200
1010 500 (as it has 19 ProfileNo)

Please help me in doing this task.

Regards,
Gita

Comments

  • 105967
    105967 Member Posts: 1,027
    You could use
    SQL> select ep.emp_no, ep.salary - nvl(ded.red_amount, 0) salary
    2 from emp_profile ep,
    3 ( select emp_no, sum(red_amount) red_amount
    4 from emp_profile
    5 where profile_no in (19,45,67,89,99,24)
    6 group by emp_no
    7 ) ded
    8 where ep.profile_no = 11
    9 and ded. emp_no(+) = ep.emp_no
    10 order by ep.emp_no
    11 ;

    EMP_NO SALARY
    ---------- ----------
    1000 1200
    1010 500

    2 rows selected.

    SQL>
  • 536807
    536807 Member Posts: 115
    Many Thanks !!!!
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    I used simple case.
    And I used searched case.
    create table empTable(EmpNo,ProfileNo,Salary,Red_Amount) as
    select 1000,11,1200,430 from dual union
    select 1000,12, 100, 55 from dual union
    select 1000,13, 120, 7 from dual union
    select 1000,14, 430, 8 from dual union
    select 1000,15, 300, 40 from dual union
    select 1000,43, 200, 20 from dual union
    select 1000,56, 100,232 from dual union
    select 1010,11,1000,300 from dual union
    select 1010,14, 110,700 from dual union
    select 1010,19, 200,500 from dual union
    select 1010,12, 410,545 from dual union
    select 1010,13, 300, 54 from dual;
    select EmpNo,
    case max(case when ProfileNo in(19,45,67,89,99,24)
    then 1 else 0 end)
    when 1 then max(decode(ProfileNo,19,Red_Amount))
    else max(Salary) end as Salary
    from empTable
    group by EmpNo;
    EmpNo  Salary
    ----- ------
    1000 1200
    1010 500
This discussion has been closed.