Macros in TeraData

Macros

Creating a Macro in Teradata

Create macro< macro  name>(<params>)AS(SQL Queries);  

Replacing a Macro in Teradata

Replace macro< macro  name>(<params>)AS(SQL Queries);  

Dropping  a Macro in Teradata

Drop macro< macro  name>  

Viewing a Macro in Teradata

SHOW macro< macro  name>  

Executing a Macro in Teradata

Exec < macro  name>(<params>) OR Execute< macro  name>>(<params>) CREATE macro mc – party2 (partyid1 integer)

As(Select* From party where party id =:party id1 or party name> :party income1;)

Exe mc- party2 (2,20000)

Inclined to build a profession as Teradata Developer? Then here is the blog post on, explore Teradata Training

Using Macros to preserve the integrity

MACROS may be used for either of the following purposes: To ensure data integrity that the data conforms to certain or value restrictions To ensure referential integrity that the data being input as foreign keys have legal primary key values else wherein the database Create as a macro which inserts a new party member row with the following restrictions Party must be over 21 years of age Party must be assigned a valid party code. Create macro new – party (pid integer, pname varchar(30),pcode integer, Hired data, birth date)

Teradata Interview Questions

As(Rollback work ’Invalid Here’)

Where(:Hired- birth)/365<21;

Rollback work ’Invalid party code’

Where: pcode not in (Select party code from a party where party code =: pacode);

Insert into the party(Party id, party name, party code, j date)

Values(:pid: p name, :p code: hired););  

Fail  

exe new party (19,’ meer’, 40,’2010-08-06’,’2010-08-06’)

Success

exe new  party(19,’ meer’,40,’2010-08-06,’’1981-08-06’)