• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1133
  • Last Modified:

Create a temporary table from another table

How do I create a temporary table from another table and display the new table?
I'm trying this, but I get, "4184 rows affected by the last command, no resultset returnet".

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
0
Rick
Asked:
Rick
1 Solution
 
Terry WoodsIT GuruCommented:
I'm used to using this syntax:
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
into temp _temptable

Open in new window

0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I'm trying this, but I get, "4184 rows affected by the last command, no resultset returnet".
that's normal, you CREATE the table (step 1), and then, in a second statement, you need to query that table.
0
 
RickAuthor Commented:
angelIII,

OK. I ran the first query, then "SELECT * FROM _temptable;", but I got, "Table 'db_tnlrpts._temptable' doesn't exist."

Thanks,
Rick
0
 
UmeshCommented:
Here is the solution for your problem.......... :-)

http://www.experts-exchange.com/Database/MySQL/Q_23915202.html
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now