Nested Case Statement in Terada

Like all other database Teradata support nested Case Statement. I will describe nested case with simple example. First We create a small example to work with nested case. Make a temporary table which called 'SKN_COUNTRY' and insert dummy data to the table.

create volatile table SKN_Country
 (Country varchar(50)
,CITY varchar(50)
,CITY_CODE Decimal(15,0))
on commit preserve rows

insert into SKN_Country Values ('DK','CPH',001);
insert into SKN_Country Values ('DK','Aalborg',002);
insert into SKN_Country Values ('DK','Arhus',003);
insert into SKN_Country Values ('SE','Stockholm',001);
insert into SKN_Country Values ('SE','Gothenburg',002);
insert into SKN_Country Values ('NO','Oslo',001);

Country	CITY	CITY_CODE
DK	Aalborg	2
DK	Arhus	3
DK	CPH	1
NO	Oslo	1
SE	Stockholm	1
SE	Gothenburg	2

Approach 1 (Failed):
-------------------------
select
case T1.Country
 WHEN 'DK'
 Then
 case when T1.CITY='CPH'
 then 'DK-CPH'
 else 'other-DK'
end
When 'SE'
case when T1.CITY='Stockholm'
 then 'SE-STOCKHOLM'
 else 'other-SE'
end

END as  CITY_TYPE_CODE
FROM SKN_Country AS T1

But unfortunately, above approach will not work . You will get error "SELECT Failed. 3707:  Syntax error, expected something like a 'BETWEEN' keyword or an 'IN' keyword or a 'LIKE' keyword or a 'CONTAINS' keyword between a string or a Unicode character literal and the 'case' keyword. "

Approach 2 (Success):
----------------------------
select T1.country,
case When T1.Country='DK' Then
  case T1.CITY
    When 'CPH' THEN 'DK-CPH'
    ELSE 'NODK'
   END
 WHEN T1.Country='SE' THEN
  case T1.CITY
    When 'stockholm' THEN 'SE-stock'
    ELSE 'noSE'
   END
END as CITY_TYPE_CODE
FROM SKN_Country AS T1

Result Set:
-------------

Country	CITY_TYPE_CODE
DK	NODK
DK	NODK
DK	DK-CPH
NO	?
SE	SE-stock
SE	noSE

Approach 3 (Success):
---------------------------
Select T1.country,
CASE When T1.Country='DK' THEN
case when T1.CITY='CPH'
then 'DK-CPH'
else 'otherDK'
end
ELSE
Case when T1.country='SE' then
Case when T1.CITY='Stockholm' then
'SE-stock'
else 'other.SE'
end
end
END as  CITY_TYPE_CODE
FROM SKN_Country AS T1

Result Set:
-------------

Country	CITY_TYPE_CODE
DK	otherDK
DK	otherDK
DK	DK-CPH
NO	?
SE	SE-stock
SE	other.SE