Informix - Delete temp table if exists

4
votes

I'm trying to delete a temp table when I run a query. I cannot find the answer and already searched for documentation.

Basically, what I want to do is check if a table exists; if it does exist, delete it and proceed with the select so that the results can be inserted into the temp table. If it doesn't exist, well, just create the TEMP table so that the results can be inserted.

I'm using Informix 11.70

2
sqlinformix

2 Answers

5
votes

If it is supported in 11.70 (it is in 12.10), then the syntax is:

 DROP TABLE IF EXISTS temp_table_name;

The 11.70 manual for DROP TABLE indicates that it exists in 11.70 as well as 12.10.

Then run your query with the INTO TEMP temp_table_name clause to recreate the table.

2
votes

you can also add an exception block such as:

Begin
    On Exception in (-206)
    End Exception with Resume;

    Drop table <<myTempTable>>;
End;