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);
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:
-------------
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:
-------------
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 |
No comments:
Post a Comment