Teradata -GROUPING SET with Example

In Data-warehouse, Grouping means aggregating values to calculate some meaningful measures. In EDW, we have fact tables which holds measures and calculate some aggregates like SUM, MAX, MIN, AVG etc. In most of the cases, we will use GROUP BY clause however in some cases you may want to calculate measures over different axis. It can be single column or multiple columns combined. This is very useful when you are creating data cubes where each dimension will show you different…

Continue Reading
Create Table in Teradata

Teradata Create Table Example with options

One of the most basic query one must know is to CREATE table in Teradata. In RDBMS, Tables are databases objects which are used to store data in form of rows and columns. Let us see a very basic example below on how to create table in Teradata and what all options are available while creating a table. Click on the Following options to see how it affects the CREATE Table in Teradata. Option 1 :SET MULTISET Option 2 :VOLATILE…

Continue Reading
Teradata SQL Position

Teradata SQL Position function with Example

Teradata SQL Position function returns the starting position of substring in a given string. If the substring is not present in the string then it returns 0. Teradata SQL Position is very useful when you need to extract some part of the string based on some specific pattern. Let us see an example for POSITION function: CREATE MULTISET VOLATILE TABLE VT_ACTORS ( S_NO INTEGER, FULL_NAME VARCHAR(30) ) PRIMARY INDEX(S_NO) ON COMMIT PRESERVE ROWS; INSERT INTO VT_ACTORS SEL 1, ‘WOODY ALLEN’…

Continue Reading
Teradata SQL NULLIF Command

Teradata SQL NULLIF expression with Example

Teradata SQL NULLIF expression is used for setting some specific value to NULL. It compares two values and if they match then returns NULL else returns the first value which is passed to it. Use Below Queries to test TERADATA SQL NULLIF expression yourself: CREATE VOLATILE TABLE TST_CASE ( EMPID INTEGER, NAME VARCHAR(30), YEARS_OF_EXP SMALLINT, SALARY INTEGER ) PRIMARY INDEX (EMPID) ON COMMIT PRESERVE ROWS; INSERT INTO TST_CASE VALUES(1,’WILLIAM’,5,50000); INSERT INTO TST_CASE VALUES(2,’SARA’,3,30000); INSERT INTO TST_CASE VALUES(3,’ROBIN’,-1,40000); INSERT INTO TST_CASE…

Continue Reading
COALESCE in Teradata

Teradata SQL COALESCE statement with Example

Teradata SQL COALESCE expression is used for NULL Handling. It returns the first not null value which is passed to it. If all the values passed to COALESCE function are NULL then output would be NULL else output would be first NOT NULL value encountered. Use Below Queries to test TERADATA SQL COALESCE expression yourself: CREATE VOLATILE TABLE TST_CASE ( EMPID INTEGER, NAME VARCHAR(30), YEARS_OF_EXP SMALLINT, SALARY INTEGER ) PRIMARY INDEX (EMPID) ON COMMIT PRESERVE ROWS; INSERT INTO TST_CASE VALUES(1,’WILLIAM’,5,50000);…

Continue Reading
Teradata SQL CASE statement with Example

Teradata SQL CASE statement with Example

Teradata SQL CASE expression is used to check for various conditions sequentially and produce result depending on which condition is met first. If no condition is matched then TERADATA SQL CASE will result in output specified in ELSE clause. If ELSE clause is missing then, it will result in NULL as value. So, if you have to test multiple conditions sequentially and apply the first condition met ; then you should use CASE statement. Don’t forget to put END keyword…

Continue Reading