Functions in TeraData
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 party;
TRIM ([Expression] Leading and training Blanks/Binary zeros
TRIM (Both From[Expression]Leading an training blanks/brings zeros
Trim (Train LING From[Expression])Trailing blanks/binary zeros
Trim(Leading from[Expression]) Leading blanks/binary zeros
Aspired to become a Teradata Developer? Explore the post to discover the know-hows on
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 sub string 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 party;
Add Months
Adding 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-Strubg)
AS CLAUSE
It used for taking alias name and we can use alias any where in the query
Ex- Select party income as pi from 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 party;
Data types Attribute functions
Title - Display title of the column
Format - Display Format of the column
Named - Display Column name
Type - Display The date type of the column if there no title column name is title
Select title(party name), Format(Party name), named(party name),type(party name )from party;
Learn more about Teradata Interview Questions in this blog post.
Sample - Displays sample number of rows
1.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)AS R 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 party;
Merge - It updates, if Record exist otherwise it insert
Ex - Merge in to party using values(1,’meera’,30)As D(pid,pnm)
On D. Pid- party id
When matched then
Up date 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 ouptpu is 003% then it displays3%
Select party in come(Format’XC3’)From party;
Select JDate, Jdate(Format ’yyyyBmmm/DD’)From party;
For Indepth knowledge on Teradata click on: