Link to home
Start Free TrialLog in
Avatar of Rick
Rick

asked on

Temporary table doesn't exist?!?

I'm using the following query to create a temporary table:

CREATE TEMPORARY TABLE _temptable
SELECT _itemroot AS 'ITEM ROOT',
CASE WHEN _umex = 'FT' THEN (1 + 0)
WHEN _umex = 'LB' THEN (1 + 1)
WHEN _umex = 'EA' THEN (1 + 2)
ELSE 'No UM' END AS 'YIELD'
FROM tbl_tnlrpts
WHERE _workcenter = 'EXTPACK' AND _date BETWEEN '2008-03-30' AND '2008-03-30'
ORDER BY _itemroot


This runs OK with no warnings... but when I try to query the newly created table (eg, SELECT * FROM _temptable) I get, "Table db_mydata._temptable doesn't exist". What do you mean, "it doesn't exist"? Where did it go? How can I query it?


Thanks,
Rick
Avatar of Umesh
Umesh
Flag of India image

Please can you post exact error message that you are getting? also post MySQL version
Avatar of Rick
Rick

ASKER

MySql 5.1

I'm running the above query in MySQL Query Browser.

Error:

The query could not be executed.
! Description
! Table 'db_mydata._temptable' doesn't exist.
ErrorNr. 1146


Can you try this from command line?? and see what it reports

Login to MySQL... locate mysql/bin

mysql -uUserName -pSecret

mysql>


Avatar of Rick

ASKER

Humm, it works from the command line.

When I run the first query (to crate the temp table), I get:

Query OK, 3596 rows affected (0.47 sec)
Records: 3596 Duplicates: 0 Warnings: 0

When I run the select query (SELECT * FROM _temptable), I can see all my data.

Why is this not working from MySql query browser?
Avatar of Rick

ASKER

In QueryBroser, I use:

CREATE TEMPORARY TABLE _temptable
SELECT _itemroot AS 'ITEM ROOT',
CASE WHEN _umex = 'FT' THEN (1 + 0)
WHEN _umex = 'LB' THEN (1 + 1)
WHEN _umex = 'EA' THEN (1 + 2)
ELSE 'No UM' END AS 'YIELD'
FROM tbl_tnlrpts
WHERE _workcenter = 'EXTPACK' AND _date BETWEEN '2008-03-30' AND '2008-03-30'
ORDER BY _itemroot;
SELECT * FROM _temptable

The temp table is probably being discarded at the ";" right after _itemroot.

Is there a way to make this work from the QueryBroser?


Thanks,
Rick
Yes, I was sure that it would work from commandline..

I've never worked on QueryBrowser...No issues..Can you send me the table details and some sample data.. Will try at my end.
Avatar of Rick

ASKER

db_tnlrpts is InnoDB

Sample data:

_workcenter         _date                 _itemroot             _umex

EXTPACK          2008-03-30        US786BLK           FT
EXTPACK          2008-03-30        US785WHT          FT
EXTPACK          2008-03-30        UK1212GH           LB
EXTPACK          2008-03-30        UK4091TX            LB
EXTPACK          2008-03-30        LA459BLK            EA
EXTPACK          2008-03-30        LA459WHT           EA
EXTPACK          2008-03-30        OEM331FK            ?
EXTPACK          2008-03-30        OEM332FK            ?


Thanks,
Rick
Pls.. send me the table definition and data in sql format :-)
ASKER CERTIFIED SOLUTION
Avatar of Umesh
Umesh
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Rick

ASKER

Excellent! Thank you.