Global Temporary Tables and Host Variables
March 31, 2010 Ted Holt
|
Global temporary tables are a marvelous, powerful feature of SQL, allowing SQL programmers to create temporary tables as needed in order to build complex queries. One problem you will encounter occurs when you want to use a host variable within a program to limit the amount of data loaded into the table. You may not use a host variable in a Declare Global Temporary Table command. Fortunately, this is an easy limitation to circumvent. First, let’s look at an example of the problem. In the following code fragment, I attempt to create a global temporary table using data for one customer only.
D MyPgm
D inCompany 3p 0 const
D inCustomer 5p 0 const
/free
exec sql
drop table session/Temp1;
exec sql
declare global temporary table Temp1 as
(select *
from sales
where companyno = :inCompany
and customerno = :inCustomer)
with data;
This does not compile. In fact, even SEU won’t accept it. The solution is to create the file and load it in two steps. Here’s one possibility.
exec sql
drop table session/Temp1;
exec sql
declare global temporary table Temp1 as
(select * from sales)
definition only;
exec sql
insert into session/Temp1
(select *
from sales
where companyno = :inCompany
and customerno = :inCustomer);
The phrase “definition only” in the declaration tells the system to create the table based on the fields in the select clause, but not to put any data into the table. Once the table exists, I load it with an insert, which does accept host variables. I rarely use this technique. In this example, I intentionally kept the select simple, pulling data from one table only, but it would be more realistic to pull data from more than one table, like this:
select h.companyno, h.customerno, h.saledate, h.shipdate,
h.termcode, t.termdesc,
co.custname, co.class,
co.address1, co.address2, co.city,
co.state, co.postalcode, d.lineno, d.itemno,
d.quantity, d.price,
d.quantity * d.price as extended,
i.itemdesc, i.itemtype, i.itemclass
from saleshdr as h
join customer as cu
on cu.companyno = h.CompanyNo
and cu.customerno = h.CustomerNo
join salesdtl as d
on h.orderno = d.orderno
join items as i
on d.itemno = i.itemno
join company as co
on cu.companyno = co.companyno
join term as t
on h.termcode = t.termcode
where companyno = :inCompany
and customerno = :inCustomer);
Imagine replacing both of the select statements in the first example with that or worse. The first select would not have to have the where clause, but having to keep the rest of the two instances of the select in sync when changing the query seems less than ideal to me. The first technique is OK for very simple queries, but most queries are not simple. Here’s a second technique that uses similar principles and doesn’t require duplication of the select statement.
exec sql
drop table session/Parms;
exec sql
declare global temporary table Parms
(CompanyNo dec(3,0), CustomerNo dec(5,0));
exec sql
insert into session/Parms
values(:inCompany, :inCustomer);
exec sql
drop table session/Temp1;
exec sql
declare global temporary table Temp1 as
(select h.*
from session/Parms as p
join sales as h
on p.companyno = h.CompanyNo
and p.customerno = h.CustomerNo)
with data;
In this example, I create two temporary files–Temp1 to hold the sales data and another one called Parms. The Parms table has only one row (record), into which I load the values of the host variables. From there, I use joins (often an inner join, as in this example, but also often a cross join) to access the host variable values in queries. I find this technique more useful, especially when the values of the host variables are called for in the building of many temporary tables within a program. The ability to create global temporary tables is a great boon for SQL programming, and fortunately, the lack for support for host variables in global temporary table definitions is not a show-stopper.
|
