Global Temporary Tables And Host Variables, Take 2
May 31, 2016 Ted Holt
In Global Temporary Tables and Host Variables, I complained that DECLARE GLOBAL TEMPORARY TABLE does not permit me to use host variables. I presented two ways to circumvent the problem. Today I revisit the topic to show another way that was not available six years ago.
First, create some global variables.
create variable somelib.gv_Company dec(3); create variable somelib.gv_Customer dec(5);
Then use those global variables in your program.
exec sql set gv_Company = :inCompany; exec sql set gv_Customer = :inCustomer; exec sql declare global temporary table Temp1 as (select * from sales where companyno = gv_Company and customerno = gv_Customer) with data with replace;
DECLARE GLOBAL TEMPORARY TABLE has no qualms with the use of global variables.
Global variables are still new to me. I suspect I have not even begun to understand the many ways to use them effectively.
Ted Holt welcomes your comments and questions. Email him through the IT Jungle Contacts page.