Data Services Macro Syntax

Data services macros pass specifically defined parameters using SQL Select statements. The syntax for these operations must be precise.

All macros have the same first 2 parameters. The first parameter is the temporary table’s alias name. The requested data is placed into this temporary table when extracted from the database. The name for the temporary table must be unique. This is important when you need to prefix a column name with the table name. For example:

SELECT * FROM NAME_OF_MACRO

The second parameter is a WHERE clause. Using a WHERE clause enables you to narrow the scope of the data extraction, thus reducing the amount of binary data that is parsed. This is the most critical factor in the overall performance of data services macros. For example:

SELECT * FROM PES_MACRO_INVESTMENT(tmp, cls_UID = 1, Budget)

is calling for the budget attribute of portfolio. PES_MACRO_INVESTMENT is the name of the data services macro, tmp is the name of the temporary table in SQL, and cls_UID = 1 is a WHERE clause that reduces the amount of work data services must perform by parsing only the portfolio binaries. This parameter can be left blank to return back all the rows from the table.

Macros can also be embedded into another macro’s WHERE clause. The following example is a macro that returns back all the project tasks whose owning item is active. For example:

SELECT * FROM PES_MACRO_PROJECT_TASK(task, obj_ProjectUID IN

or:

(SELECT obj_UID FROM PES_MACRO_INVESTMENT(inv, , Active) WHERE inv.Active = 1), Completed)

Once the macro is processed and the temporary table is created and filled with data, data services replace the macro with the table name along with the alias that was used in the first parameters. Once all macros are processed, the original SQL statement, with the macro replaced with temporary table names, is passed on to SQL Server to process. This gives the ability to use SQL to process further into the desired results.