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.
