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:

1. Select party income, Null if(party income,50000)from party’
2. 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:

1. Select Jdate, CAST(J Date As varchar(7))From pary—Date—String
2. 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;

Teradata Interview Questions

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