Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Finding value for a specific condition

536807Jul 21 2008 — edited Jul 22 2008
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

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
Many Thanks !!!!
Aketi Jyuuzou

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
1 - 3
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Aug 19 2008
Added on Jul 21 2008
3 comments
1,888 views