Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: How to write CASE in WHERE cause?

    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.

    Code:
    Sample data:
    ORGANIZATION_UNIT    YEAR      COSTS
    ----------------------------------------------
    UNIT_A                         2001       100
    UNIT_A                         2003       110
    UNIT_B                         2001        50
    UNIT_B                         2003        40
    UNIT_C                         2001      1000
    UNIT_C                         2003      1200
    etc

    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.
    Last edited by grofaty; 10-07-03 at 07:44.

  2. #2
    Join Date
    Jan 2003
    Location
    Zutphen,Netherlands
    Posts
    256
    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
    Ties Blom
    Senior Application Developer BI
    Getronics Healthcare
    DB2,ORACLE,Powercenter,BusObj,Access,
    SQL, SQL server

  3. #3
    Join Date
    Dec 2002
    Posts
    134
    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

Posting Permissions

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