Teradata macro

Quoting from reference: http://www.teradatahelp.com/2010/09/macro-is-teradata-extension-to-ansi-sql.html

A macro is a Teradata extension to ANSI SQL that contains pre written SQL  statements. Macros are used to run a repeatable set of tasks.The details of macro can be found in data dictionary (DD)  . Macros are database objects and thus they belong to a specified user or database. A macro can be executed by Queryman. ,  BTEQ, by another macro. 


How to create a Macro
Create a macro to generate a DOB list for department 321:

CREATE MACRO DOB_Details AS 
(SELECT  first_name ,last_name  ,DOB
FROM TERADATA.employees
WHERE dept_numbr =321 
ORDER BY DOB asc;);

EXECUTE a Macro 
To execute a macro, call it along with  the exec command. 
EXEC DOB_Details; 

last_name    first_name    DOB
Ram            Kumar         75/02/22 
Laxman       Sinha           79/04/06 

Coalesce function

If the field is null, the coalesce function can assign a default value:

select coalesce(type_desc, 'Unknown'), count(*)
from person
group by type_desc

SQL Case statement

select  
pos_num as "Position number",
case
        when status = 1 then 'Active'
        when status = 2 then 'Inactive'
        else 'Unknown'
end as "Status"
from dim_position

Teradata: How to fetch the latest transaction from a history of transaction?

An example:

select  
 empl_hist.*
from  dim_empl_hist empl_hist
qualify
 1 = row_number() over(partition  by empl_hist.empl_num order by empl_hist.empl_hist_key desc)
where
 empl_status = '1'

Teradata SQL date format

An example:
trans_ts <= cast('2013-02-16' as date format 'yyyy-mm-dd')