Temporary tables

Often, it is difficult to get all the information you need to generate a report together in one single query. ASM's custom report editor allows you to enter multiple SQL instructions, separated by a semi-colon (;)

This works just like any SQL console editor.

You can use this feature to create temporary tables of information and populate them with multiple queries. The only stipulation ASM makes is that the last query in your set MUST be a SELECT query (returning the results of the temporary table).

ASM will automatically detect your usage of CREATE TEMPORARY TABLE and clean them up for you, so you do not need to add ``DROP TABLE x'' queries.

Eg: This simple query set will aggregate all the animal names into a separate table which can then be output by a custom report:

CREATE TEMPORARY TABLE tt$USER$ ( aname varchar(50) );

INSERT INTO tt$USER$ (aname) SELECT AnimalName FROM animal;

SELECT * FROM tt$USER$

Note the use of the $USER$ SQL tag to make sure the temporary table is unique for each user. The ASM custom report editor is capable of syntax checking all queries and autogenerating the correct HTML from a multi-query set.