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