0

I'm trying to create temporary table from stored procedure on this way;

CREATE TEMPORARY TABLE IF NOT EXISTS tempUvozTB engine = memory AS 
( 
  SELECT
   `rnuvozstavke`.`BrojRacuna`,
  `pfrnstavke_i`.`TB`,
  `pfrnstavke_i`.`Kolicina`,
  `pfrnstavke_i`.`Tezina`,
  `pfrnstavke_i`.`Vrijednost`,
  `rnuvozstavke`.`Kolicina` AS UvozKol
FROM
  `pfracun_i`
  INNER JOIN `pfrnproizvodi_i` ON `pfracun_i`.`BrojRN` =
`pfrnproizvodi_i`.`BrojRacuna` AND `pfracun_i`.`Godina` =
`pfrnproizvodi_i`.`Godina`
  INNER JOIN `pfrnstavke_i` ON `pfrnproizvodi_i`.`BrojRacuna` =
`pfrnstavke_i`.`BrojRacuna` AND `pfrnproizvodi_i`.`Godina` =
`pfrnstavke_i`.`Godina`
  INNER JOIN `rnuvoz` ON `pfracun_i`.`BrojOdobrenja` = `rnuvoz`.`VezaKupca`,
  `rnuvozstavke`
WHERE
  `pfracun_i`.`BrojOdobrenja` = pBrojOdobrenja AND
  `rnuvozstavke`.`BrojRacuna` = pBrojRN
GROUP BY
  `pfrnstavke_i`.`SifraSirovine`,
  `pfracun_i`.`BrojOdobrenja`,
  `pfrnstavke_i`.`TB`,
  `rnuvozstavke`.`BrojRacuna`
  );

SELECT tempUvozTB.TB, SUM(tempUvozTB.Kolicina * tempUvozTB.Tezina * tempUvozTB.UvozKol) AS UkTezina,  SUM(tempUvozTB.Vrijednost * tempUvozTB.UvozKol) AS UkVrijednost from tempUvozTB GROUP BY tempUvozTB.TB;
DROP TEMPORARY TABLE IF EXISTS tempUvozTB;

But then I get error like mydatabase.tempuvoztb doesn't exist. What I'm doing wrong?

Josef
  • 2,648
  • 5
  • 37
  • 73

1 Answers1

-1

Are you using auto commit with MySQL? If not you probably need to commit your transaction before trying to select from the temporary table.

If this is not working, why not just create a normal table and drop it afterwards? If you keep using the particular naming convention (prefix temp) you could always clean up those tables later instead of relying on the database engine to do the work.

Edited:

I found this link for you which may help with your issue

MySQL stored procedures with temp tables

Community
  • 1
  • 1
Andre Van Zuydam
  • 651
  • 7
  • 12
  • I've tried with commit but still the same. Also I don't understand how this works when it is not inside stored procedure... – Josef Oct 12 '16 at 10:14
  • Now I added line DROP TEMPORARY TABLE IF EXISTS tempUvozTB; before creating temp table but then I get a new problem. It doesn't recognize parameter in where clause. I get error like Unknow column 'pBrojOdobrenja" in 'where clause' – Josef Oct 12 '16 at 10:19