“Don't count the days. Make the days count.”
The 14 rules of Macros
Macros are database extensions and reside in the Data Dictionary in DBC.
CREATING and EXECUTING a Simple Macro
Only ONE transaction will run by executing any macro. Every Macro will have a semi-colon to end each SQL statement and an additional semi-colon to end the CREATE.
Multiple SQL Statements inside a Macro
CREATE Macro Emp2_mac AS
(SELECT | * FROM Employee_Table |
WHERE | Dept_No = 400 ; |
SELECT | * FROM Department_Table |
WHERE | Dept_No = 400 ; ) ; |
Two Queries
EXEC Emp2_mac ;
Still, only ONE transaction ran by executing the macro here. Everything in a macro is considered ONE transaction. Notice we have two SQL statements that end with a semi-colon, but we also have a semi-colon to end the CREATE MACRO Statements.
Complex Joins inside a Macro
CREATE Macro Join_Mac AS
(SELECT E.*, Mgr_No, Budget
FROM Employee_Table as E
INNER JOIN
Department_Table as D ON E.Dept_No = D.Dept_No ; ) ;
Users can create complex joins in macros and then SHARE them with other Users.
Passing an INPUT Parameter to a Macro
The IN_Dept_No represent INPUT Parameters in the above Macro. We must place something within the parentheses or the macro will not work, because it is expecting the USER to tell it the value of IN_Dept_No in the EXEC statement.
Troubleshooting a Macro with INPUT Parameters
This Macro expected an Input Parameter and errors unless it gets it.
Troubleshooting a Macro with INPUT Parameters
This Macro expected an Input Parameter and errors unless it gets it.
An UPDATE Macro with Two Input Parameters
This Macro expected two parameters so we placed them in the proper order (Position). Since the CREATE statement listed both, and IN_Employee_No was listed first, it will be in the first position for parameters in the EXEC statement inside brackets.
Executing a Macro with Named (Not Positional) Parameters
Both Exec statements are the same except how they pass the Macro Input Parameters.
Troubleshooting a Macro
This Macro failed because you can't pass an input parameter to a FROM CLAUSE.
3.144.47.218