## Forum Stats

• 3,734,027 Users
• 2,246,861 Discussions

Discussions

#### Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

# Finding value for a specific condition

Member Posts: 115
edited July 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)

Regards,
Gita

• 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> ```
• Member Posts: 115
Many Thanks !!!!
• 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.