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?

Hi,

Does ur select statement have a where clause?

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

this query will work fine.

do you have space in your temp dbspace?

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?

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.

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"?