To Use or Not To Use Decode?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: To Use or Not To Use Decode?

  1. #1
    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!

  2. #2
    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. #3
    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;
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

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