Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    19

    Unanswered: INTO TEMP not working

    Hey All,

    When I run my SQL against Informix, I can query data just fine. However, when I add INTO TEMP at the end of my query, I get the following message:

    "The specified table (tmp_all) is not in the database. (#-206)."

    For some reason, INTO TEMP isn't working, even though I can run SELECT queries.

    Anyone know why this is happening?

  2. #2
    Join Date
    Aug 2003
    Location
    India
    Posts
    262
    Hi,

    Does ur select statement have a where clause?

    select * from emp where empno = 10 into temp tmp_emp;

    this query will work fine.

  3. #3
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    do you have space in your temp dbspace?

  4. #4
    Join Date
    Aug 2004
    Posts
    19
    Yes, a WHERE clause is included. Here's a snippet:

    SELECT
    *
    FROM
    daily_hist
    WHERE
    file_date = '05/19/04' AND
    prin_bal <= 0 AND
    corp_adv <> 0
    INTO TEMP tmp_all;

    Not being a DBA, I don't think I can check to see wether or not I have space for TEMP. Might there be a query I can run to check this?

  5. #5
    Join Date
    May 2004
    Location
    New York
    Posts
    248
    execute this:

    database sysmaster;

    select name[1,8] dbspace,
    sum(chksize) Pages_size,
    sum(chksize) - sum(nfree) Pages_used,
    sum(nfree) Pages_free,
    round ((sum(nfree)) / (sum(chksize)) * 100, 2) percent_free
    from sysdbspaces d, syschunks c
    where d.dbsnum = c.dbsnum
    group by 1
    order by 1;


    the result will show you how much space you have in your dbspaces

    also you can execute a onstsat -d command that will sho you the free space also.

  6. #6
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    I would think that executing a query that is strictly "select ... from ... where" would not be a problem for any user but if you cannot execute a "select ... from ... where ... into ...." statement then perhaps this is a problem with the userID and/or directory permissions if you're using cooked space.

    Even though it's a temp table that your referencing (selecting into) you are still writting to the database with the intention of creating something & you may not have permission to create a table even if it is temporary.

    What happens if you login as the owner of the database which is probably userID "informix"?

Posting Permissions

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