Informix Stored Procedure

[kookie15120]

I have developed an informix stored procedure below. The procedure creates several temp tables, then joins them for the final result.
When I run the SP the first time in Query Analyzer, it works.
Second time I receive an error message;
[Informix][Informix ODBC Driver][Informix]The specified table (turret_inventory_temp) is not in the database.
Third time, it works.
Fourth time, error.
And so on.

Any suggestions?
Thanks


CREATE PROCEDURE livtur.rsp_turret_steel_inventory_report () returning char(10), char(3), char(8),
char(8), char(2),char(15),char(12),date,char(6),char(8),char(15),char(8),char(30),
dec(10,2),dec(10,2),dec(10,2),dec(10,2),dec(10,2),dec(10,2),dec(10,2),char(3),
char(8),char(10),char(1),char(2), char(8), char(6), char(8), char(15), char(8);

define a char(10); --inv_item_control_nbr 1
define b char(3); --inv_warehouse 2
define c char(8); --inv_po_nbr 3
define d char(8); --inv_vendor_id 4
define e char(2); --inv_po_prefix 5
define f char(15); --inv_heat 6
define g char(12); --inv_tag_nbr 7
define h date; --inv_bought_date 8
define i char(6); --inv_form 9
define j char(8); --inv_grade 10
define k char(15); --inv_size 11
define l char(8); --inv_finish 12
define m char(30); --inv_extended_finish 13
define n dec(10,2); --inv_book_weight 14
define o dec(10,2); --inv_material_cost 15
define p dec(10,2); --inv_value 16
define q dec(10,2); --inv_order_weight 17
define r dec(10,2); --inv_reserved_weight 18
define s dec(10,2); --pod_bal_wgt 19
define t dec(10,2); --pod_pod_rcvd_wgt 20
define u char(3); --pod_pod_po_itm 21
define v char(8); --pod_pod_ven_id 22
define w char(10); --pod_pod_itm_ctl_no 23
define x char(1); --pod_pod_trcomp_sts 24
define y char(2); --pod_pod_po_pfx 25
define z char(8); --pod_pod_po_no 26
define aa char(6); --pol_frm 27
define ab char(8); --pol_grd 28
define ac char(15); --pol_size 29
define ad char(8); --pol_fnsh 30

ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_Inventory_temp;
END EXCEPTION WITH RESUME;
ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_PO_log_temp;
END EXCEPTION WITH RESUME;
ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_PO_detail_temp;
END EXCEPTION WITH RESUME;

--Select Inventory Items
SELECT intpcr_rec.pcr_itm_ctl_no INV_Item_control_nbr,
intprd_rec.prd_whs INV_Warehouse,
intpcr_rec.pcr_po_no INV_PO_nbr,
intpcr_rec.pcr_ven_id INV_Vendor_ID,
intpcr_rec.pcr_po_pfx INV_PO_prefix,
intprd_rec.prd_heat INV_Heat,
intprd_rec.prd_tag_no INV_Tag_nbr,
intpcr_rec.pcr_agng_dtts INV_Bought_date,
intprd_rec.prd_frm INV_Form,
intprd_rec.prd_grd INV_Grade,
intprd_rec.prd_size INV_Size,
intprd_rec.prd_fnsh INV_Finish,
intprd_rec.prd_ef_svar INV_Extended_Finish,
sum(intprd_rec.prd_bk_wgt) INV_Book_weight,
avg(intprd_rec.prd_bk_mat_cst) INV_material_cost,
sum(intprd_rec.prd_bk_mat_val) INV_Value,
sum(intprd_rec.prd_ord_res_wgt) INV_Order_weight,
sum(intprd_rec.prd_qte_res_wgt) INV_Reserved_weight
FROM livturdb:informix.intprd_rec intprd_rec,
livturdb:informix.intpcr_rec intpcr_rec
WHERE (intprd_rec.prd_itm_ctl_no=intpcr_rec.pcr_itm_ctl_no)
GROUP BY
intpcr_rec.pcr_agng_dtts,
intprd_rec.prd_whs,
intprd_rec.prd_tag_no,
intprd_rec.prd_frm,
intprd_rec.prd_grd,
intprd_rec.prd_size,
intprd_rec.prd_fnsh,
intprd_rec.prd_ef_svar,
intpcr_rec.pcr_po_no,
intpcr_rec.pcr_ven_id,
intpcr_rec.pcr_po_pfx,
intprd_rec.prd_heat,
intpcr_rec.pcr_itm_ctl_no
into temp TURRET_Inventory_temp;

--Select PO Log information
select distinct lpi_po_no POl_po_no,
lpi_po_itm POl_po_itm,
lpi_frm POl_frm,
lpi_grd POl_grd,
lpi_size POl_size,
lpi_fnsh POl_fnsh
from potlpi_rec
into temp TURRET_PO_log_temp;

--Select PO Detail information with PO Log information
SELECT potpod_rec.pod_bal_wgt,
potpod_rec.pod_rcvd_wgt POd_pod_rcvd_wgt,
potpod_rec.pod_po_itm POd_pod_po_itm,
potpod_rec.pod_ven_id POd_pod_ven_id,
potpod_rec.pod_itm_ctl_no POd_pod_itm_ctl_no,
potpod_rec.pod_trcomp_sts POd_pod_trcomp_sts,
potpod_rec.pod_po_pfx POd_pod_po_pfx,
potpod_rec.pod_po_no POd_pod_po_no,
POl_frm,
POl_grd,
POl_size,
POl_fnsh
FROM livturdb:informix.potpod_rec potpod_rec
left join TURRET_PO_log_temp TURRET_PO_log_temp
on pod_po_no = POl_po_no
and pod_po_itm = POl_po_itm
WHERE potpod_rec.pod_trcomp_sts<>'C'
into temp TURRET_PO_detail_temp;

--Return report records
foreach c1 for
select inv_item_control_nbr, inv_warehouse, inv_po_nbr, inv_vendor_id,
inv_po_prefix, inv_heat, inv_tag_nbr, inv_bought_date, inv_form,
inv_grade, inv_size, inv_finish, inv_extended_finish, inv_book_weight, inv_material_cost,
inv_value, inv_order_weight, inv_reserved_weight, pod_bal_wgt, pod_pod_rcvd_wgt,
pod_pod_po_itm, pod_pod_ven_id, pod_pod_itm_ctl_no, pod_pod_trcomp_sts, pod_pod_po_pfx,
pod_pod_po_no, pol_frm, pol_grd, pol_size, pol_fnsh
into a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t,
u, v, w, x, y, z, aa, ab, ac, ad
from TURRET_Inventory_temp left join TURRET_PO_detail_temp
on inv_item_control_nbr = pod_pod_itm_ctl_no
and inv_vendor_id = pod_pod_ven_id
and inv_po_nbr = pod_pod_po_no
return a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t,
u, v, w, x, y, z, aa, ab, ac, ad with resume;
end foreach;

END PROCEDURE

 

[PHV]

I'd replace this:
ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_Inventory_temp;
END EXCEPTION WITH RESUME;
ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_PO_log_temp;
END EXCEPTION WITH RESUME;
ON EXCEPTION IN (-206, -958)
DROP TABLE TURRET_PO_detail_temp;
END EXCEPTION WITH RESUME;
with this:
BEGIN ON EXCEPTION END EXCEPTION WITH RESUME DROP TABLE TURRET_Inventory_temp; END
BEGIN ON EXCEPTION END EXCEPTION WITH RESUME DROP TABLE TURRET_PO_log_temp; END
BEGIN ON EXCEPTION END EXCEPTION WITH RESUME DROP TABLE TURRET_PO_detail_temp; END

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kookie15120]

Thank you PH. Worked Great! Have a great day!