Menu ▾ ▴

SP and Temporary table

Help
Mamo
2011-05-31
2012-09-19

  • Mamo

    Mamo - 2011-05-31

    Hi all,

    below my real SP that works fine if called by a vb6 proc

    but returns 'test.py:21: Warning: Unknown table 'tmppostilettodisponibili'
    cur1.callproc( "logistica.postoLettoDisponibile", (row,0) )

    BEGIN

    DROP TEMPORARY TABLE IF EXISTS logistica.tmppostilettodisponibili;

    CREATE TEMPORARY TABLE logistica.tmppostilettodisponibili

    ( idPostoLetto int(10) unsigned NOT NULL,

    idCamera int(10) unsigned NOT NULL,

    Codice varchar(45) NOT NULL,

    piano tinyint(4) NOT NULL,

    camera varchar(45) NOT NULL,

    PRIMARY KEY (idPostoLetto)

    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

    INSERT INTO logistica.tmppostilettodisponibili

    SELECT postoletto.idPostoLetto, postoletto.idcamera,postoLetto.Codice,
    camera.piano, camera.camera

    FROM (logistica.postoLetto

    INNER JOIN logistica.camera ON postoLetto.idCamera = camera.idCamera)

    LEFT JOIN dipendenti.Accasermamento A ON postoletto.idPostoLetto=
    A.idPostoLetto

    WHERE ((A.dataFine IS NULL AND A.perID IS NULL)

    OR (A.dataFine IS NOT NULL AND A.perID IS NOT NULL))

    AND (idAlloggio = p_idAlloggio)

    ORDER BY camera.piano, RIGHT(CONCAT('0000',camera),3), postoLetto.Codice;

    DELETE FROM logistica.tmppostilettodisponibili

    WHERE idPostoLetto IN

    (SELECT A.idPostoLetto FROM dipendenti.Accasermamento A

    WHERE A.perID IS NOT NULL AND A.dataFine IS NULL)

    AND idPostoLetto <> p_idPostoLetto;

    END $$

    Any suggestions?

     

Log in to post a comment.