# Thread: To Use or Not To Use Decode?

1. Junior Member
Join Date
Oct 2001
Posts
22
Hi All,
ok, here's the scenario:
Each employee has a type (1,2,3) and I want to select salary for emp's who have type in (1,2) each, so I wrote:

select dept, decode(type, 1, sum(salary*.4)) sal1,
decode(type, 2, sum(salary*.5)) sal2
from emp
where type in (1,2);

but i get 2 rows returned:

dept sal1 sal2
------ ----- -----
101 555
101 666

any help rendered is appreciated...asap please.....
thanx
oh, and if i can get out of it sal1+sal2 at the same time is appreciated!

Join Date
Apr 2001
Location
Czechia
Posts
712
Hi,
something like that?
Code:
```SELECT dept, sal1, sal2, sal1+sal2 as sal12 FROM
(
select
dept,
0.4*sum(decode(type, 1, salary, 0)) sal1,
0.5*sum(decode(type, 2, salary, 0)) sal2
from emp
where type in (1,2)
GROUP BY dept
)```

3. Super Moderator
Join Date
Dec 2000
Location
Ljubljana, Slovenia
Posts
4,439
Or in case you can't use inline view as in ales's solution, you can use this simple query:

select deptno,
sum(decode(type, 1, sal*0.4, 0)) sal1,
sum(decode(type, 2, sal*0.5, 0)) sal2,
sum(decode(type, 1, sal*0.4, 2, sal*0.5, 0)) sal12
from emp
where type in (1,2)
group by deptno;

4. Of course, you would never actually want to bury those constants in a SQL statement. It would be much better to have the .4 and .5 stored in the EmpType_T table:

Code:
```EmpType_T:
EmpType_ID	SalMod
1		0.4
2		0.5
3		1```
Then, the SQL would be:

Code:
```SELECT
E.DeptNo,
SUM(DECODE(TYPE, 1, E.SAL * ET.SalMod, 0)) SAL1,
SUM(DECODE(TYPE, 2, E.SAL * ET.SalMod, 0)) SAL2,
SUM(DECODE(TYPE, 1, E.SAL * ET.SalMod, 2, E.SAL * SalMod, 0)) SAL12
FROM
Emp			E	,
EmpType_T	ET
WHERE
E.EmpType	IN	(1,2)			AND
ET.EmpType	=	E.EmpType	AND
ET.EmpType	IN	(1,2)
GROUP BY
E.DeptNo;```
- Chris

#### Posting Permissions

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