ETL Job via SAS DI Studio:Why Precode and Postcode properties of a job is disabled?

If you are an ETL developer and working with the tool called SAS DI Studio. A bad day in the morning could be started like; you are not able to insert code in the Precode and Postcode box of your job properties. The check box for Precode and Postcode is disabled. It's maybe nonsense, you never seen this kind of error before, right??!! Precode and Postcode of the job property look like below:

                       Fig 1: Precode and Postcode of the Job Property is disabled.

What Can go wrong?

If you look at the below picture, you will find three different type of code generation mode.

                         Fig 2: Three different type of Code generation mode

Generally Code generation mode 'Automatic' is selected by default.

                                        Fig 3: By default 'automatic' is selected.

If we want to look at the user defined code then usually we select 'All user Written' which is look like below:

                                    Fig 4: Code generation mode 'All user written' selected.

By mistakenly, if you choose 'all user written' and click 'yes' (see below image) then code generation mode will be changed and saved.

                                Fig 5:Saving code generation mode 'All user written'.

At this situation, if you click on properties and want to insert Precode and Postcode for the job, you will not be able add Precode and Postcode for the job. The option is disable for the job.

How you can Enable Precode and Poscode?

To enable Precode and Postcode we need to change code generation mode from 'All user written' to 'Automatic' and save it.

Then you will be able to add Precode and Postcode in the job and property screen will look like below:

                                        Fig 6: Enabled Precode and Postcode

SQL Command- WITH Clause (CTE)

Rather writing jargon words I will go straight to the code:

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

 

Select * from EMP_HIER

When I have seen the above code, first thing I have in my mind is; WITH clause is used to make derived table  ‘EMP_HIER’ by using a source table ‘V_Employee’. And it must be used instead of making volatile table.

And then tried to run only first part:

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

 

Ya!! that was stupid move!! It doesn’t work separately; it works when select statement is there. So when I run whole code, then it works fine J

WITH EMP_HIER(EMP_ID,MGR_ID,EMP_NAME)

AS

(Select

EMP_ID,MGR_ID,EMP_NAME

from V_Employee)

Select * from EMP_HIER

Now next question came in my mind why I am going to use the above syntax, instead I can create volatile table. I did some investigation and share my thoughts with my colleagues and so far I found reason for using ‘WITH’ Clause is below:

1)     Data retrieve will not be slower

Explanation: 

When we make volatile table by getting data from other table then we generally don’t create table structure for volatile table. So your volatile table doesn’t have index automatically. Whereas if you use WITH clause then you are directly accessing to the source table so it will be faster than accessing to volatile table. 

 2)      Portability

Explanation: 

There is different syntax for making volatile in different database systems. E.g.

--In MS SQL:

CREATE TABLE #Temp1

( Name Char( 30 ), seqid integer )           

--In Teradata:

create volatile table V_Employee

(

EMP_ID int,

MGR_ID int,

EMP_NAME varchar(80)

)

on commit preserve rows;

But ‘WITH clause’ is same for both MS SQL and Teradata database. So you don’t need to change the syntax if your code needs to work for the both databases.

3)  Not enough pool space to create volatile table

Explanation: 

Sometimes you may not have enough pool space to create volatile table in your development environment and then you can use WITH clause to do the same.

In addition,  my study found, ‘WITH clause’ will be helpful when recursive data query will be needed e.g. find out different level of manager of a particular employee in a big organization. However, I will cover recursive queries in other post.

FYI, ‘WITH clause’ has official name which called Common Table Expression (CTE).

Insert data from Excel to Terdata table via SQL assistant.

Sometimes you need to get data from excel to Teradata table. There are a few ways you can import data from excel to Teradata; one of them is using Teradata SQL assistant. I am going to explain step by step process of inserting data into Teradata table from an excel data sheet.

For example, you have customer information in the excel (Fig 1) and want to import the excel data to the teradata table.

Fig 1: Data in Excel

Step 1: Save excel as .csv file
First you need to save the excel file as .CSV

Fig 2: Save as .CSV

Step 2: Remove cloumn names from the excel
You can edit the .csv file like below (Fig:3 )

Fig 3: Edit .csv file

Then remove the column name from the .csv file. (See fig:4)

Fig 4: remove column names

Step 3: Create a temp table to insert the data from Excel

Create a volatile table in teradata (see fig: 5), SQL Code for creating the volatile table:

create volatile table Customer_Info
(Customer_no  int,
 Cutomer_First_Name varchar(30),
 Customer_LastName varchar(30),
 Phone int)
 on commit preserve rows;

Fig 5: Create table to store data from excel

And there are no records in the table.
                                       

Fig 6: Newly created table, waiting to get data from .csv

Step 4: Choose 'Import Data' from SQL Assistant

From Teradata SQL Assistant, go to File->Import data and click at Import data

Fig 7: Import data from Teradata SQL assistant.

And then you will see message "Ready for Import operation" at the above of SQL editor in teradata SQL assistant. (see Fig:8)

                                             Fig 8: Message 'Ready for Import operation'

Step 5: Execute insert statement

Now you need to write below SQL statment and press F5

                                             Fig 9: SQL syntax for inserting data
When you press F5 then you will find option to select your .csv file that you have completed in step 1 and step 2. (See figure 10)

Fig 10: Choose .csv file to get data into the table

When you select the .csv file SQL assistant import functionality will start taking the data from .csv and will insert records into the table 'Customer_Info', you will find message at the bottom of the windows (see fig;11)

Fig 11: data imported to the table

Now you can select the table to test if the table is filled by the data from the excel.

Note: Please click again 'Import data' from File->Import data to disable import mode in SQL assistant.

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. 

Analytical function: ROW_NUMBER () with Qualify in Teradata

This post will help to understand QUALIFY  ROW_NUMBER OVER (PARTITION BY ...) phrase with simple example.

We take an example like below:

INDUST_CODE	INDUSTRY_NAME	ACTIVATE_DATE
1011	GOOGLE	13-12-2009
1013	YOUTUBE	01-09-2008
1011	GOOGLE	15-12-2009
1012	MICROSOFT	14-10-1999
1011	GOOGLE	11-12-2009

The data listed above have more than one row with Industry named ‘Google’.  Business needs the industry name without duplication, they don’t want one industry name more than once. They like to see the data like below:

INDUST_CODE	INDUSTRY_NAME
1011	GOOGLE
1012	MICROSOFT
1013	YOUTUBE

How do you do that?

You are expert in SQL, so wrote the script like below which works fine:

select INDUST_CODE,INDUSTRY_NAME

from INDUSTRY

QUALIFY

row_number() Over (partition by INDUST_CODE order by ACTIVATE_DATE desc)=1

If you want to know how the code worked? Then please find the explanation step by step:

1) Group by (Partition by)with Industry Code (INDUST_CODE)

2) Order by with ACTIVATE_DATE which means latest date will be used to find the first row

3) Then uses ‘QUALIFY’ to select the row.

Lets explain the syntax :

Make the translation easy:

qualify row_number() over (Partition and order by brace) = 1

    which means,

    Selecting first row from the result set after applying group by and order by function. 

Extra Attention while using Multiple Tables in Update Statement (UPDATE From SELECT Statement/Using JOIN in UPDATE Statement)

While updating data in a table you may discover error with “Error: 3993 Illegal usage of alias name” , it will be really difficult to find out at first glance what went wrong?
As you wrote the code, at least you did not expect to have alias error. You are almost sure that you did not do any mistake like alias problem.

Let’s have a look at the piece of code:

Update ACCOUNTS AS ACC
FROM
(SELECT
 UPD_ACC.ACC_NO
,UPD_ACC.DEB_AMT
FROM CUSTOMER AS CUST
JOIN
TRANSACTIONS AS TRN ON
 CUST. City_ID =TRN.City_ID
AND CUST.Customer_ID=TRN.Customer_ID
Where TRN.CTY='NY') AS UPD_ACC
SET ACC.ACC_NO= UPD_ACC.ACC_NO
     ,ACC.DEB_AMT= UPD_ACC.DEB_AMT
Where
ACC.Customer_ID= UPD_ACC.Customer_ID
And ACC. City_ID =UPD_ACC.City_ID

After you run the code, you can’t expect to have alias error because you have used alias correctly in the code.

However, the main reason of the error is; you are not allow to use alias left side of the SET syntax like you did at above:  Set ACC.ACC_NO= UPD_ACC.ACC_NO

So correct syntax will be: Set ACC_NO= UPD_ACC.ACC_NO

As well as, at where clause you need to use the table name not the alias(the table you are updating).

e.g. 

Where  

        ACCOUNTS.Customer_ID= UPD_ACC.Customer_ID --NOT ACC.Customer_ID

And ACCOUNTS. City_ID =UPD_ACC.City_ID

Now we can fix the code and rewrite again like below which will work without error:

Update ACCOUNTS 

FROM 

(SELECT

 UPD_ACC.ACC_NO

,UPD_ACC.DEB_AMT

FROM CUSTOMER AS CUST

JOIN 

TRANSACTIONS AS TRN ON

CUST. City_ID =TRN.City_ID

AND CUST.Customer_ID=TRN.Customer_ID

Where TRN.CTY='NY') as UPD_ACC

SETACC_NO= UPD_ACC.ACC_NO

     ,DEB_AMT= UPD_ACC.DEB_AMT

Where  

         ACCOUNTS.Customer_ID= UPD_ACC.Customer_ID

AND ACCOUNTS. City_ID =UPD_ACC.City_ID