Advantages of OLAP Functions over Subqueries in Teradata

Significance of OLAP Features:

1. Execution Time of the Query is minimized to a significant level after using OLAP features.
2. Less complex query can be formulated by OLAP functions.

Description of the table [PRDT_RECVD_DT_TBL] used:

Table contains the 5 columns namely PRODUCT, BRAND, CATEGORY, SALES_CENTER (i.e Store), PRDT_RCVD_DATE. Consider 'Product, Brand, Category' as a MERCHANDISE.

High level Requirement Specification:

Ultimate Goal is to compute the Earliest Product Received Date. Earliest Product Received Date is the most recent date when majority of the sales center received the goods at the Merchandise level.

Teradata Date and Time functions

The following are the date and time functions in Teradata.

DATE or CURRENT_DATE -  Both displays current date.
ADD_MONTHS(date,3) - Add 3 months to the current date.
ADD_MONTHS(date, -2)- Subtract 2 months from the current date.
TIME or CURRENT_TIME - Both displays current time.
EXTRACT( Day FROM Date)- Extracts and displays the day.
EXTRACT(Month FROM Date) - Extracts and display month.
EXTRACT(Year FROM Date) - Extracts and displays year.
CURRENT_TIMESTAMP - Displays combination of both date and time.
EXTRACT( Hour FROM Time) - Extracts and displays hour.
EXTRACT( Minute FROM Time) - Extracts and displays Minute.
EXTRACT( Second FROM Time) - Extracts and displays Second.

What is the Criteria to choose best Primary Index ?

Be careful while choosing the primary index because it affects the data storage and performance.

The following are the important tips while choosing the primary index.

1. Data Distribution.

You need to analyze the number of distinct values in the table . If the primary index of the table contains less number of null values and more distinct values,it will give better the performance.

2. Access frequency. 

The column has to be frequently used in the where clause during the row selection.

The column should be that which is frequently used in join process.

3. Volatility

The column should not be frequently changed.

Decimal datatype issue with BTEQ

Suppose if you have a table called employee having column salary declared as decimal(35,0).

Query: Select salary from employee;

The above query works fine in SQL Assistant. But if you try to run using BTEQ, the output contains decimal point(dot)  at the end. See below.

salary

******

98765432109876543210987654321098765432.

If you can try FORMAT '99999......999999' , you will get error because format allows only 30 digits. Alternative solution for this problem is use FORMAT 'Z(35)' or FORMAT '9(35)' instead of Decimal(35,0) to avoid dot at the end of the output if your query is running through BTEQ.