Functions in TeraData

Ratings:
(4)
Views: 0
Banner-Img
Share this blog:

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:

 

You liked the article?

Like: 0

Vote for difficulty

Current difficulty (Avg): Medium

EasyMediumHardDifficultExpert
IMPROVE ARTICLEReport Issue

About Author

Authorlogo
Name
TekSlate
Author Bio

TekSlate is the best online training provider in delivering world-class IT skills to individuals and corporates from all parts of the globe. We are proven experts in accumulating every need of an IT skills upgrade aspirant and have delivered excellent services. We aim to bring you all the essentials to learn and master new technologies in the market with our articles, blogs, and videos. Build your career success with us, enhancing most in-demand skills in the market.