CASE WHEN with fall through

General

CASE WHEN with fall through

I want to use the CASE statement for multiple groupings, such as:

SEL

CASE country

WHEN 'US' THEN 'North America'

WHEN 'US' THEN 'World'

WHEN 'UK' THEN 'EU'

WHEN 'UK' THEN 'World'

...

END AS cntry_grp

, COUNT(1)

FROM countries

GROUP BY 1

Is there some way to force a fall through after the first match? This would be equivalent to leaving out the 'break;' statements in a JAVA switch statement.

The only way I could think of solving this is by using a table and repeatedly insert the same rows under different namings (US --> North America, US --> World, etc.). Do you know a more elegant solution?

Thanks,

Michael

Tags (1)
2 REPLIES

Re: CASE WHEN with fall through

I've figured out myself. Any further ideas still welcome.

CREATE VOLATILE TABLE Revenues (

Revenue INT,

COUNTRY_DESC VARCHAR(20),

COUNTRY_ID INTEGER

) ON COMMIT PRESERVE ROWS;

CREATE VOLATILE TABLE Hierarchy (

PARENT_ID INTEGER,

PARENT_DESC VARCHAR(20),

CHILD_ID INTEGER

) ON COMMIT PRESERVE ROWS;

INSERT INTO Revenues (Revenue,COUNTRY_DESC,COUNTRY_ID) VALUES (1000,'US',1);

INSERT INTO Revenues (Revenue,COUNTRY_DESC,COUNTRY_ID) VALUES (100,'CA',2);

INSERT INTO Revenues (Revenue,COUNTRY_DESC,COUNTRY_ID) VALUES (400,'UK',3);

INSERT INTO Revenues (Revenue,COUNTRY_DESC,COUNTRY_ID) VALUES (300,'DE',4);

INSERT INTO Revenues (Revenue,COUNTRY_DESC,COUNTRY_ID) VALUES (150,'FR',5);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (100,'NA',1);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (100,'NA',2);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (101,'EU',3);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (101,'EU',4);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (101,'EU',5);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (1000,'WORLD',100);

INSERT INTO Hierarchy (PARENT_ID,PARENT_DESC,CHILD_ID) VALUES (1000,'WORLD',101);

SEL

CASE GROUPING(hyr.PARENT_DESC) WHEN 1 THEN 'Total' ELSE hyr.PARENT_DESC END AS PARENT

, CASE GROUPING(rev.COUNTRY_DESC) WHEN 1 THEN 'Total' ELSE rev.COUNTRY_DESC END AS CHILD

, SUM(rev.Revenue)

FROM Revenues rev

JOIN Hierarchy hyr

ON rev.COUNTRY_ID = hyr.CHILD_ID

GROUP BY ROLLUP(hyr.PARENT_DESC,rev.COUNTRY_DESC)

ORDER BY 1,2

Re: CASE WHEN with fall through

How to find the every one hour loading the data to target dwh

insert,updated,deleted recorrds identifications

plz send me the script