## Wednesday, January 21, 2015

### 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:
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
, 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:

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
, 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
, 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.