The following query uses a TEMP table. I get the results in dbaccess, but
the query fails when submitted from a java program using JDBC 2.21.jc3...
"table temp0 not found... SQLCODE 206 ISAM 111". I also noticed two
additional threads in 'onstat -u' after running this query from java. I
expected to see only one additional thread. Any thoughts on what may be
wrong? Thanks.
(some background to explain this odd-looking query... We are having
performance problems using multiple VTS_CONTAINS in a single SELECT. We
found that the following query, using TEMP table, works much faster to
simulate out business rule. Using ACCRUE or OR clauses of VTS_CONTAINS does
not implement the business rules)
++++++++++++++++++++++++++++++++++++++++++++
create temp table temp0 (asset_id integer);

insert into temp0
SELECT DISTINCT q.asset_id
FROM asset q, asset_data a0
WHERE (q.asset_group_id IN (1, 190994))
AND q.asset_id = a0.asset_id
AND a0.field_type_id IN
(998934, 998935, 998936, 998938, 998939, 998940, 998933)
AND (VTS_CONTAINS(a0.value, '2003-08-11'));

insert into temp0
SELECT DISTINCT q.asset_id
FROM asset q, asset_data a1
WHERE (q.asset_group_id IN (1, 190994))
AND q.asset_id = a1.asset_id
AND a1.field_type_id IN (998906, 998907, 189995, 998905)
AND (VTS_CONTAINS(a1.value, 'population')) ;

create index temoix on temp0 (asset_id);
update statistics for table temp0;

SELECT DISTINCT q.asset_id, UPPER(s.value)
FROM temp0 q, OUTER asset_data s
WHERE s.field_type_id = 998934
AND s.asset_id = q.asset_id;

drop table temp0;