Temporary Tables

THE SQL can be made up of multiple statements. For example temporary table(s) can be created and populated with INSERT and UPDATE statements. These can use sub queries and accept user parameter values entered by users. The final SELECT statement is the one which will actually be used to generate the report. This final SELECT statement can not have sub queries. Typically the steps would be :-

1. Create one or more temporary tables as required
2. Populate with these with UPDATE and INSERT using user parameters as required (<Condition> tags)
3. Include a final SELECT statement which uses the temporary tables linked to the temporary tables. This can include user parameters (<Condition> tags) in its own right.
4. Use DROP TABLE to drop the temporary tables (See DROP TABLE below if <Orderby> tag is used)
5. Add <Orderby> tags as required

The DROP tables only applies to the OrderBy tag, which while still supported should not be used in future reports, because it will not work on large reports. Use  OutputTableOrderby instead.

Uses

Temporary tables can be created within XML reports and have a variety of uses.

• To hold information which is to be used within several SQL statements
• To build list of keys for use within the main query. For example identifying Location down through the Location hierarchy.
• To hold the fields for the main output data so that the output records can be built in several stages.

Placing within the XML Report

Temporary tables are created as part of the SQL statements. In the standard construction for a report the SQL Select and From clauses are put outside the <Where> tags and the appropriate <Where> tag is evaluated and applied when the report is executed. When temporary tables are used the creating and populating Temporary tables and the final SQL statement special considerations apply. These are covered in detail in Where tag (Advanced).

SQL - CREATE TABLE

Only temporary tables can be created. These are identified with a # suffix and this must be included in the table name. The following example illustrates how this is normally used and includes examples of the data types normally required. It is best to always specify the Collate statement (COLLATE SQL_Latin1_General_CP1_CI_AS ) when creating CHAR, VARCHAR and TEXT data types.  The PRIMARY KEY statement is optional, but may improve performance, When naming the Tables keep the names short. It is best not to inlcude spaces in the tables or field names. Also avoid SQL reserved words.     

SQL for Populating Temporary Table

The INSERT command is used to populate the temporary table. There are a number of different ways of constructing these. The following is appropriate in most circumstances.The select element can include WHERE clauses derived from <Condition> tags.

INSERT INTO #Locations (Location_Key,Item_Name,Preferred)
SELECT Location_Key,  Item_Name,Preferred FROM Location_Name

 Once there are records in the table they can be updated. using the UPDATE statement.

Create Table #L (location_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
Custodian  varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #L (Location_key) Select location_key from Location
UPDATE  #L set Custodian  =  Location.Custodian
FROM Location
WHERE  Location.Location_Key  = #L.Location_Key

OR

Create Table #L (location_key char(16) COLLATE SQL_Latin1_General_CP1_CI_AS,
Custodian  varchar(8) COLLATE SQL_Latin1_General_CP1_CI_AS)
INSERT INTO #L (Location_key) Select location_key from Location
UPDATE  #L set Custodian  =  Location.Custodian
FROM Location
INNER JOIN  #L ON  #L.Location_Key = Location_Key

It is also possible to use WHILE/WEND loops. These are useful for expanding hierarchies. The following SQL is used  for expanding a selected Location to include its key and the keys for all Locations below it in the hierarchy.

CREATE TABLE #Locations (Location_Key CHAR(16) COLLATE SQL_Latin1_General_CP1_CI_AS PRIMARY KEY,
      Parent_Key CHAR(16)  COLLATE Database_Default)

INSERT INTO #Locations (LOcation_Key,Parent_Key)
SELECT L.LOCATION_KEY, L.LOCATION_KEY 
FROM 
LOCATION L 
WHERE 
<Condition field= "L.Location_key" operator="equal" type="Location"  name="Select Locations" entrycount="-1" />
WHILE 1=1 BEGIN
 INSERT INTO #Locations
  SELECT L.Location_Key, Tinc.Parent_Key
  FROM Location L
  INNER JOIN #Locations Tinc on L.Parent_Key=Tinc.Location_Key
  LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key
  WHERE Texc.Location_Key IS NULL
 IF @@ROWCOUNT=0
  BREAK
END

DROP TABLE

The temporary table needs to be dropped after final use. If it is not then the report may not open for a second time until Recorder is closed and re-opened. If you have not used the <Orderby> attribute then the  DROP TABLE statement (eg. DROP TABLE #MainResults) can be placed immediately before the closing </where > attribute. If the <Orderby> attribute has been used then DROP TABLE needs to be incorporated into the SQL of the <Orderby> attribute, (eg.  
<Orderby name="by Taxon Group/preferred_Name" SQL="Order By tg.taxon_group_name, itn.preferred_name DROP TABLE #MainResults "/> )

Note that this is not the case with the <OutputTableOrderBy. DROP TABLE should be included at the end of SQL.    < /FONT>