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 information and you can drill down to get more info. In Teradata, GROUP BY GROUPING SETS allows you to do exactly same. In a single
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 GLOBAL TEMPORARY Option 3 :NO Yes Option 4 :NO Yes Option 5 :NO Yes Option 6 :NO Yes Option 7 :NOT NULL NULL Option 8
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’ ; INSERT INTO VT_ACTORS SEL 2, ‘JAY LENO’ ; INSERT INTO VT_ACTORS SEL 3, ‘JERRY SEINFELD’ ; Let’s find existence of string “EN” in name
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 VALUES(4,’TED’,7,70000); INSERT INTO TST_CASE VALUES(5,’LILY’,-1,20000); SEL * FROM TST_CASE; EMPID NAME YEARS_OF_EXP SALARY 1 WILLIAM 5 50000 2 SARA 3 30000 3 ROBIN -1 40000
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); INSERT INTO TST_CASE VALUES(2,’SARA’,3,30000); INSERT INTO TST_CASE VALUES(3,’ROBIN’,NULL,40000); INSERT INTO TST_CASE VALUES(4,’TED’,7,70000); INSERT INTO TST_CASE VALUES(5,’LILY’,NULL,20000); SEL * FROM TST_CASE; EMPID NAME YEARS_OF_EXP SALARY 1
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 to finish CASE statement else you will get syntax error. If you wish to give alias name to the column mention it after END keyword.