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 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

Teradata Training Program.

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

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

  1. 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: