GTTs return no data

Database

GTTs return no data

I'm using queryman to create a GTT as follows:

create GLOBAL TEMPORARY table MANAGER.mytest (
WK_ID SMALLINT)
primary index (WK_ID)

;insert into MANAGER.mytest
select distinct WK_END_ID_NU WK_ID
from MAINSCHEMA.MyCalendar abc
where (abc.CAL_DT >= DATE '2006-08-24'
and abc.CAL_DT <= DATE '2007-08-16')

The insert finishes without an error and I see the message at the bottom that says 'Completed. 52 rows processed'.

When I then try to read from mytest table, I get nothing back.

sel * from MANAGER.mytest

---returns nothing---

However, when I run just the select statement used to populate mytest, it returns 52 rows.

select distinct WK_END_ID_NU WK_ID
from MAINSCHEMA.MyCalendar abc
where (abc.CAL_DT >= DATE '2006-08-24'
and abc.CAL_DT <= DATE '2007-08-16')

---returns 52 rows------

In Queryman, I can see mytest having been created under the MANAGER schema, but somehow, the insert statement hasn't worked.

I'm left scratching my head here. Any advice or pointers?

TIA...

2 REPLIES

Re: GTTs return no data

I got it. I needed an 'On Commit Preserve Rows' statement after the table creation
Enthusiast

Re: GTTs return no data

ya exactly. otherwise it is storing data in the GTT table jsut during finishing the satement...
so when u are firing another statement it gets deleted...