Functions
a)To do some operations by loading or retrieving [Arithmetic, mathematics, etc operations]
b) To implement “ETL” Functionality etc
Coalesce
- It returns 1st, not null value
- It can be also be used for returning user Defined values in case of null
Syntax:
Coalesce(Column or expression,<user define values>)
Ex: Select party name, coalesce (party name, ’unknown’)From party
Ex: coalesce(permanent, temporary, email id, contracting ‘Details not available’)
Description
Getting the non- empty column value or control information.
Null LIF
It returns null faith Arguments are equal
Syntax
Null IF(Argument1,Argument2)
Ex:
- Select party income, Null if(party income,50000)from party’
- Select 100/(null if(10-10-10,0))—it prevents from division by zero
TRIM
It removes both left and right side spaces
Syntax- TRIM (Column or expression)
Ex- Select party Name, TRIM (Party name) from the party;
TRIM ([Expression] Leading and training Blanks/Binary zeros
TRIM (Both From[Expression]Leading a training blank/brings zeros
Trim (Train LING From[Expression])Trailing blanks/binary zeros
Trim(Leading from[Expression]) Leading blanks/binary zeros
Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training
Character or chars
Displays the No.of character in the string
Syntax- Chars (Column or expression)
Ex: Select party name, chars(party name) from party
Length
Display the length of the argument[Can be integer, string, date]
Syntax- Length(Argument)
Ex: Select Length(‘Vinay’)
Select Length(‘100’)
Sub str
Display substring from the specified to position to the length in the main string
Syntax- Sub str(Column or expression, startpos, Length)
Ex- Select party name, sub str(party name 3,2)From party;
Position
Display the first occurrence position of a character or string in columns expression
Syntax- Position(Character or string in column or expression)
Ex: Select party name, Position(‘a’ in party name)from the party;
Add Months
Adding a specified number of month to the date and column
Syntax- Add-months(Date column,<No of Months>)
Ex- Select Jdate, Add- months (jadate,5) from party
CAST
It converts one data type to another data type [Integer to string date to string etc]
Syntax- CAST(Column Or Exp As new data type);
Ex:
- Select Jdate, CAST(J Date As varchar(7))From pary—Date—String
- Select CAST(‘1234’ As char(3)--; Integer-String)
AS CLAUSE
It used for taking alias name and we can use alias anywhere in the query
Ex- Select party income as pi from a party where pi<50000;
CASE Expression
It Evaluates Against multiple conditions[Instead of multiple id else conditions we go for this]
Syntax- CASE
When condition1 Then STMT1
When condition2 Then STMT2
When condition3 then STMT3
Else STMTN
End
Ex:
Select party id, party name, party income CASE When party income<=30000 Then ’poor’ When party income>30000 And party income<50000 Then ’Avg’ Else ’Good’ End As Rating from the party;
Data types Attribute functions
Title - Display title of the column
Format - Display Format of the column
Named - Display Column name
Type - Display The data type of the column if there no title column name is the title
Select title(party name), Format(Party name), named(party name), type(party name )from the party;
Sample
- Displays sample number of rows
Syntax: Select* From party sample N;
Ex: Select * from party smple10;
- Displays sample present age number of rows
Syntax: Select*from party sample N;
Ex: Select * from party sample 20;
Rank
Displays the party Rank of the column
Select party income, Rank(Party income)ASR from party Quality R<5;
RANDOM
Displays the RANDOM value between range specified
Syntax - Random(Low value, High value)
Ex - Select party id, party name, Random(20,30) from the party;
Merge
It updates, if the Record exists otherwise it inserts
Ex - Merge into a party using values(1,’Meera’,30)As D(pid,pnm)
On D. Pid- party id
When matched then
Update set party=D.PNM
When Not varchar then
Insert values(D.pid, D. PNM, D. ped)
Format commend
It is used to change the format of integer, strings, and dates
Ex: Select party income, party income(Format’$99,999,99’)
From party;
Select(1000/party income)*100(Format ‘z9%’)As
“Increase percentage” From party;-- it the output is 003% then it displays3%
Select party income(Format’ XC3’)From party;
Select JDate, Jdate(Format ’yyyyBmmm/DD’)From party;
For In-depth knowledge on Teradata click on:
- BTEQ Features in Teradata
- Secondary Index in TeraData
- Memory Management in TeraData
- Types of OLAP’s in TeraData
- TPUMP Structure and Process In TeraData