Thursday, January 29, 2015

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

No comments: