A macro is created to execute a single or a set of SQL statements in a single execution. Each time the macro is run all of the SQL statements present inside the macro are executed and results are returned after the end of all the SQL present inside the macro.

Why do we need a macro? If there is a set of frequently used SQL statement which are being repeatedly executed, this set of SQL statement can be packed inside block and can be defined as a MACRO. A macro can include also execute another macro.

To create a macro to select today’s date:

CREATE MACRO today_date as (SELECT date;);

To execute a macro: EXEC  today_date;To drop a macro:DROP macro today_date;A macro can also be used to insert row into tables with more than one input. Here is an example of a macro with multiple inputs which inserts a row to an emp table. Make sure that the table already exists.CREATE MACRO insert_into_emp
(
 emp_no INTEGER,
 dept_no VARCHAR(12),
 first_name CHAR (20),
 last_name”CHAR (20),
 salary” INTEGER
)
AS
(
INSERT INTO emp
(emp_no, dept_no, first_name, last_name, salary)
values (:emp_no, :dept_no, :first_name, :last_name, :salary)
);

When you want the column name of the table to appear differently in your SQL query, just to make sure that you too can do some magic, you can use aliases. Though aliases can also be created for various database objects like tables, views, stored procedures and macros, it is the column aliases that are more frequently used. Aliases are also referred as correlation name. Except for having a better readability, column aliases can also be used anywhere in the SQL statement. It becomes handy especially if you are using a derived column. Let’s discuss what all you need to create an alias. First code example:

SELECT emp_no AS “Employee No”,
 dept_no AS “Department No”,
 salary “Employee Salary”,
 (salary* 12/100) “Salary Bonus”
FROM emp
WHERE “Department No”= 114
GROUP BY   1,2,3,4
ORDER BY “Salary Bonus”

Result:- 

In the above code, you can see that the columns emp_no, dept_no, salary and the derived column (salary* 12/100) are all aliased. Also note that the alias “Department No” and “Salary Bonus” as used within the SQL query and the query returns the shown result without any hiccups. So what are the secrets? Aliases should be from 1 to 30 characters long with the following guidelines: 

• Uppercase or lowercase letters (A to Z and a to z).
• Digits (0 through 9).
• The special characters DOLLAR SIGN ($), NUMBER SIGN (#), and UNDER SCORE ( _ ).
• Must NOT begin with a digit.
• Must NOT begin with an UNDER SCORE.
• NO keywords can be used as aliases.
• Alias Names can not be duplicated in the same SQL query.
• Usage of the word AS is optional.
• If there is a space used, make sure it is enclosed with only double quotes.