Hi,

I would like to write SQL witch will compare the costs by unit for year 2003 compared to the costs by unit for year 2001. I don't want to write it with 'union all' statement, I would like to write something like "CASE inside the WHERE cause", but I just cann't done it. I know that WHERE COALESCE(organization_unit,'UNIT_A')='UNIT_A' generaly works (but not for my sample), but how can this be done with CASE statement.


Bellow is SQL I would like to rewrite. UNIT_x (in bold) are variables.

select
organization_unit,
sum(costs) / (select sum(costs) from table where year=2001 and organization_unit='UNIT_A'),
from table
where year=2003 and organization_unit = 'UNIT_A'
group by organization_unit

UNION ALL

select
organization_unit,
sum(costs) / (select sum(costs) from table where year=2001 and organization_unit='UNIT_B'),
from table
where year=2003 and organization_unit = 'UNIT_B'
group by organization_unit

UNION ALL

etc.

Grofaty:

Use temp sets like this (already tested it):

SELECT #SUM1-#SUM2 FROM
(Select ORGANIZATION_UNIT AS UNIT1,SUM(COST) AS #SUM1 from TARGET.GROFATY
WHERE YEAR = 2003
GROUP BY ORGANIZATION_UNIT) TEMP1,
(Select ORGANIZATION_UNIT AS UNIT2,SUM(COST) AS #SUM2 from TARGET.GROFATY
WHERE YEAR = 2001
GROUP BY ORGANIZATION_UNIT) TEMP2
WHERE TEMP1.UNIT1 = TEMP2.UNIT2

You can use case in sum, it will save you one table/index scan

Select ORGANIZATION_UNIT,
SUM(case year when 2003 then COST end) -
SUM(case year when 2001 then COST end)
from TARGET.GROFATY
WHERE YEAR in (2003, 2001)
GROUP ORGANIZATION_UNIT