temp table creation in informix 4GL SE

Question

How should I create and populate data in a temp table? then how can I print data retrieved on temp table? I saw example like this; but no further help. SO I need some help.

      SELECT DISTINCT * FROM Table INTO TEMP Temp1;

Answer 1

You select the data just like you would from any other table:

SELECT * INTO r_temp1.* FROM Temp1

or defining cursors for such a statement, and using a FOREACH loop, or ...

The trick is that you need to know at compile time what the columns in Temp1 are going to be. In this case, you could use:

DEFINE r_temp1 RECORD LIKE Table.*

In more general cases, you'll probably assemble a record by hand from the relevant bits and pieces:

DEFINE r_temp1 RECORD
               col1 LIKE Table1.ColumnA,
               col2 LIKE Table2.ColumnB,
               ...
               colN LIKE TableZ.ColumnZ
               END RECORD

Answer 2

I created the following function that can be called passing in the temp-table name:

   function unload_temp(l_table) 
     define
     l_table char(20),
     l_file  char(20),
     str     char(512)

     let l_file = l_table clipped, ".out"
     let str = "select * from ",l_table clipped

     whenever error continue
     prepare stmt_table_exists from str
     whenever error stop

     #Return if table does not exist
     if sqlca.sqlcode = -206
     then
        return
     end if               

     unload to l_file delimiter "|" str

  end function #unload_temp() 

This will create a file called tableName.out.

This is extremely helpful when debugging and you want to see how a temp table looks in middle running a program.