ODBC - Multi-Query Prepare
I need help with ODBC. We are wanting to send a multi-query statement
through ODBC using SQL Pass-through, like the example provided below.
When we try and execute this we get the following Informix error: (#206)
"The specified table table-name is not in the database.". This error
occurs on the last SQL statement (the Union). I am guessing that each
statement must be a separate process, therefore, the temporary table is
disappearing after the execution of each SQL statement.
If we take out the lines that are creating the temporary tables and
just try to execute the first two select statements we get an Informix
error #555 (Cannot use a select or any of the database statements in a
multi-query prepare). From this error message I must assume (I know
an ASS out of U and ME) that what we are trying to do is not possible,
BUT why can I execute this type of statement from 'dbaccess' without
any problems.
Is what we are attempting to do possible?
Any help would be greatly appreciated.
EXAMPLE:
----------------------------------------------------------------------
SELECT item.item_num, sum(sa_sale_mth_sum.sale_value) af_sale_value
FROM item, sa, outer sa_sale_mth_sum
WHERE item.item_num = sa.item_num and
item.vendor_num = sa.vendor_num and
sa.sa_id = sa_sale_mth_sum.sa_id and
sa.custtype_id = 00 and
sa_sale_mth_sum.year = 1995 and
sa_sale_mth_sum.period = 1
GROUP BY item.item_num
INTO TEMP sa_00 WITH NO LOG;
SELECT item.item_num, sum(t1_a.sale_value) non_af_sale_value
FROM item, sa as SA_1, outer sa_sale_mth_sum t1_a
WHERE item.item_num = SA_1.item_num and
item.vendor_num = sA_1.vendor_num and
sa_1.sa_id = t1_a.sa_id and
sa_1.custtype_id <> 00 and
t1_a.year = 1995 and
t1_a.period = 1
GROUP BY item.item_num
INTO TEMP sa_11 WITH NO LOG;
SELECT sa_00.item_num,sa_00.af_sale_value, sa_11.non_af_sale_value
FROM sa_00,outer sa_11
WHERE sa_00.item_num = sa_11.item_num
UNION
SELECT sa_00.item_num, sa_00.af_sale_value, sa_11.non_af_sale_value
FROM outer sa_00,sa_11
WHERE sa_00.item_num = sa_11.item_num; ----------------------------------------------------------------------
+--------------------------------------------------------------------+
| Scott Allred Internet: scott.allred@nsc.sprint.com |
| Sprint/North Supply Phone: (913) 791-7000 x1810 |
| New Century, KS Fax: (913) 791-7671 |
| USA |
+--------------------------------------------------------------------+