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
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
Please can you post exact error message that you are getting? also post MySQL version
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
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>
Login to MySQL... locate mysql/bin
mysql -uUserName -pSecret
mysql>
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?
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?
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
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.
I've never worked on QueryBrowser...No issues..Can you send me the table details and some sample data.. Will try at my end.
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent! Thank you.