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

Teradata: Row returned message via Macro can make you puzzle

I am going to share a interesting problem that I found while I was executing a macro.

By looking at the result set window(above image), if you click ‘NO’ button how many rows you expect to get?  You may think how stupid question it is? It’s clearly showing ‘427755’ rows so it must be returning ‘427755’ rows.

Yap!! I believed so but I was completely wrong. 

After clicking, ‘NO’ I got 142,585 rows as a result set. Now question is, who is stupid? Me or Teradata SQL Assistant??  What’s going on inside? Let’s find out.

Let closer look at the problem. To explain the problem, I am taking similar example with small number of rows.

Say, we have two tables one is ‘Customer_Info’ and other is ‘Transaction’ Table and data look like below:

   Customer_id  Name Address         Active

101                  John 37 Lucas Aven     Y

103                  Paul 47 Honey bee       Y

105                  Mile 37 Oxford Street   Y

     

          Fig 1: Customer_Info table data

Customer_id Trn_AMT Trn_Date

101 10000   2014-11-28

102 5000 2014-11-29

103 8900 2015-01-09

105 6000 2015-01-10

                 

Fig 2: Transaction table with data

Lets join  two tables:

Select CUS.Name as Customer_Name

 , CUS.Address as Customer_Address

 , T1.TRN_AMT as Amount

 , T1.TRN_Date as Transaction_DATE

 FROM Customer_Info AS CUS

 LEFT JOIN Transaction AS T1

 on T1.customer_id=CUS.customer_id;

Which return below result set:

Customer_Name Customer_Address Amount   Transaction_DATE

Mile       37 Oxford Street 6000           2015-01-10

John       37 Lucas Aven       10000          2014-11-28

Paul     47 Honey bee 8900    2015-01-09

            

Fig 3: result set after Left joining with two tables 

And Teradata SQL assistant, show below message in the bottom pane which is exactly same as returned value.

                            

                                   Fig 4: Row returned message

However, we will try now below code:

 Delete from CUSTOMER_TRANSACTION;   --3 rows deleted

 Insert into CUSTOMER_TRANSACTION   --3 rows inserted

 select CUS.Name as Customer_Name

 , CUS.Address as Customer_Address

 , T1.TRN_AMT as TRansaction_Amount

 , T1.TRN_Date as Transaction_DATE

 FROM Customer_Info AS CUS

 LEFT JOIN Transsaction AS T1

 on T1.customer_id=CUS.customer_id;

Select * from CUSTOMER_TRANSACTION;  --3 rows selected

It means total 9 rows have been processed by above code, and exactly shows like below:

Fig 5: rows processed message

At this point, we will make a macro with above code which is:

create macro TRN_Process_by_Customer

 AS

 (

 delete from CUSTOMER_TRANSACTION;       --delete 3 rows

 insert into CUSTOMER_TRANSACTION        --insert 3 rows

 select CUS.Name as Customer_Name

 , CUS.Address as Customer_Address

 , T1.TRN_AMT as TRansaction_Amount

 , T1.TRN_Date as Transaction_DATE

 FROM Customer_Info AS CUS

 LEFT JOIN Transsaction AS T1

 on T1.customer_id=CUS.customer_id;

 select * from CUSTOMER_TRANSACTION;      --select 3 rows, which is return result

 )

And execute the macro:

exec TRN_Process_by_Customer    ---what do you expect to have message from Teradata SQL Assistant?

If you look at the beginning of this article, we have seen number of rows returned ‘427755’ but actual rows were ‘142,585’. 

And this case, it’s same, we expect to get result saying “Execute completed, 3rows returned….” But actually you got the below message. 

Fig 6: misguiding message

It means when we execute a macro it show return rows as processed rows.  To avoid confusion, message could be look like “Execute completed, 9rows processed, 3 rows returned….”

Since above example return only 3 rows so you will be happy to see that you got the right result though display message saying something different.

But for huge number of rows return can give you big confusion and you will not have any clue what’s going on until you press the “NO” button and count actual rows.