• USA : +1 973 910 5725
  • INDIA: +91 905 291 3388
  • info@tekslate.com
  • Login

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:

Summary
Review Date
Reviewed Item
Functions in TeraData
Author Rating
5

“At TekSlate, we are trying to create high quality tutorials and articles, if you think any information is incorrect or want to add anything to the article, please feel free to get in touch with us at info@tekslate.com, we will update the article in 24 hours.”

0 Responses on Functions in TeraData"

    Leave a Message

    Your email address will not be published. Required fields are marked *

    Site Disclaimer, Copyright © 2016 - All Rights Reserved.

    Support


    Please leave a message and we'll get back to you soon.

    I agree to be contacted via e-mail.