Results 1 to 3 of 3
  1. #1
    Join Date
    May 2008

    Unanswered: odbc and temporary tables

    Hi All,

    I'm having problems writing sql code from a informix odbc source in which i create a temporary table, here is basic sql code which shows what i am trying to do.

    SELECT SUM(dqty) qty,SUM(dtpr) price,dcust,delvno,dedate
    FROM delvitems,delv
    WHERE (dedate >= "01/01/06"
    AND dedate <= "31/12/06")
    AND delvnoa = delvno
    GROUP BY dcust,delvno,dedate
    INTO temp aaa;

    SELECT *
    FROM from aaa

    This code is just a test to create a temporary table for more complex reports.
    At the moment i'm using winsql to try this example, i have also used crystal reports 10 using the add command method but it still does not work .The odbc is set up correctly as i can run the above statement without the INTO temp aaa; and it works.The more complex reports i have written in informix use many temporary tables which i am trying to convert into crystal reports.
    The error message i get is table aaa does not exist.I have used the informix 2.80 odbc driver which was configured with setnet 32 and i can connect.I just can't seem to create a temporary table. I have connected as different users i.e informix as it seems to be a permissions problem. I know the above code is basic and i don't need the into temp command for it to work. it is just an example to create the temporary table.The code works fine from within informix 7.3 which is based on our unix server.

  2. #2
    Join Date
    Dec 2008

    the same problem

    i have the same problem, so if you have any solution i will be very glad
    to recive it.
    moti ulus

  3. #3
    Join Date
    Nov 2008
    Provided Answers: 1
    It is working fine for me:

    root@ufedora:/home/informix/sss# dbaccess sss - <<END
    > select sum(p) s_p, m, r
    > from test_zok
    > where p is not null
    >   and m matches "0007*"
    > group by m, r
    > INTO temp aaa;
    > SELECT *
    > FROM aaa
    > END
    Database selected.
    10 row(s) retrieved into temp table.
                 s_p m                    r
                   4 0007057203         266
                   1 0007057203         343
                   3 0007057203         650
                   8 0007058952         343
                   9 0007058952         344
                   8 0007058952         347
                   6 0007058952         355
                   6 0007058952         650
                   5 0007058952        1207
                   5 0007058952        1208
    10 row(s) retrieved.
    Database closed.
    You must not disconnect your session between select ... into temp aaa; and select ... from aaa; because scope of the temp table is session.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts