Ir para conteúdo
Fórum Script Brasil
  • 0

Erro ao criar procedure no phpmyAdmin


Leonardo Viana

Pergunta

Galera estou tetando criar uma procedure no phpMyAdmin e esta me retornando erro na compilação

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 28

usando um outro programa compila normal

so que quando do um call na procedure ela retorna um outro erro.

CREATE PROCEDURE `spDadosEmpresa`(IN CodEmpresa INTEGER(11), IN AnoInicio INTEGER(11), IN AnoFim INTEGER(11))
    NOT DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN

    CREATE TEMPORARY TABLE IF NOT EXISTS TmpNivelAcids
    (
           Num_Ano INT, 
           TFEmpQ1 DOUBLE DEFAULT 0, 
           TFEmpQ2 DOUBLE DEFAULT 0, 
           TFEmpQ3 DOUBLE DEFAULT 0, 
           TFEmpCercaSuperior DOUBLE DEFAULT 0,
           TFFTQ1 DOUBLE DEFAULT 0, 
           TFFTQ2 DOUBLE DEFAULT 0, 
           TFFTQ3 DOUBLE DEFAULT 0, 
           TFFTCercaSuperior DOUBLE DEFAULT 0,
           TGEmpQ1 DOUBLE DEFAULT 0, 
           TGEmpQ2 DOUBLE DEFAULT 0, 
           TGEmpQ3 DOUBLE DEFAULT 0, 
           TGEmpCercaSuperior DOUBLE DEFAULT 0,
           TGFTQ1 DOUBLE DEFAULT 0, 
           TGFTQ2 DOUBLE DEFAULT 0, 
           TGFTQ3 DOUBLE DEFAULT 0, 
           TGFTCercaSuperior DOUBLE DEFAULT 0
           
    ) Type = 'HEAP';

    DELETE FROM TmpNivelAcids;
    
        CALL spTaxaFrequenciaEmpQuartil(AnoInicio - 1, AnoFim, 0);
        CALL spTaxaFrequenciaFTQuartil(AnoInicio - 1, AnoFim, 0);
        CALL spTaxaGravidadeEmpQuartil(AnoInicio - 1, AnoFim, 0);
        CALL spTaxaGravidadeFTQuartil(AnoInicio - 1, AnoFim, 0);
    
            INSERT INTO TmpNivelAcids 
             (Num_Ano, 
              TFEmpQ1, TFEmpQ2, TFEmpQ3, TFEmpCercaSuperior, 
              TFFTQ1, TFFTQ2, TFFTQ3, TFFTCercaSuperior, 
              TGEmpQ1, TGEmpQ2, TGEmpQ3, TGEmpCercaSuperior, 
              TGFTQ1, TGFTQ2, TGFTQ3, TGFTCercaSuperior)
    SELECT tqtfe.Num_Ano, 
           tqtfe.Q1 AS TFEmpQ1, tqtfe.Q2 AS TFEmpQ2, tqtfe.Q3 AS TFEmpQ3, 
           (tqtfe.Q3 + (1.5 * (tqtfe.Q3 - tqtfe.Q1))) AS TFEmpCercaSuperior,
           tqtfft.Q1 AS TFFTQ1, tqtfft.Q2 AS TFFTQ2, tqtfft.Q3 AS TFFTQ3, 
           (tqtfft.Q3 + (1.5 * (tqtfft.Q3 - tqtfft.Q1))) AS TFFTCercaSuperior,
           tqtge.Q1 AS TGEmpTQ1, tqtge.Q2 AS TGEmpQ2, tqtge.Q3 AS TGEmpQ3, 
           (tqtge.Q3 + (1.5 * (tqtge.Q3 - tqtge.Q1))) AS TGEmpCercaSuperior,
           tqtgft.Q1 AS TGFTTQ1, tqtgft.Q2 AS TGFTQ2, tqtgft.Q3 AS TGFTQ3, 
           (tqtgft.Q3 + (1.5 * (tqtgft.Q3 - tqtgft.Q1))) AS TGFTCercaSuperior
    FROM TmpQuartilTaxaFrequenciaEmp tqtfe
    INNER JOIN TmpQuartilTaxaFrequenciaFT tqtfft ON (tqtfe.Num_Ano=tqtfft.Num_Ano)
    INNER JOIN TmpQuartilTaxaGravidadeEmp tqtge ON (tqtfe.Num_Ano=tqtge.Num_Ano)
    INNER JOIN TmpQuartilTaxaGravidadeFT tqtgft ON (tqtfe.Num_Ano=tqtgft.Num_Ano);
    
    SELECT Tb1.*, (AcidTipSemAfast + AcidTipComAfast) AS AcidFatTipicos,
           (AcidLevePopulacao + AcidGravePopulacao + AcidFatalPopulacao) AS AcidPopulacao,
           COALESCE((AcidFatalPopulacao * 6000 + 
            AcidGravePopulacao * 500 + 
            AcidLevePopulacao * 30) * (1000000 / NumMedioHabPop), 0) AS TaxaGravidadePopulacao,
           COALESCE((AcidLevePopulacao + AcidGravePopulacao + AcidFatalPopulacao) * 
           (1000000 / HHER), 0) AS TaxaFrequenciaPopulacao,
           
                      CASE WHEN Tb1.TaxaFrequencia IS NULL THEN 
                 '--' 
           ELSE 
                CASE WHEN Tb1.TaxaGravidade < tna.TGEmpQ1 THEN 
                     CASE WHEN Tb1.TaxaGravidade < tna.TGEmpQ2 THEN 
                          'Bom' 
                     ELSE 
                          'Medio' 
                     END
                ELSE
                     CASE WHEN Tb1.TaxaFrequencia < tna.TFEmpQ2 THEN
                          CASE WHEN Tb1.TaxaGravidade < tna.TGEmpQ2 THEN 
                               'Bom' 
                          ELSE
                               CASE WHEN Tb1.TaxaGravidade > tna.TGEmpCercaSuperior THEN
                                    CASE WHEN Tb1.TaxaGravidadeAnterior > tna.TGEmpQ3 THEN 
                                         'Grave' 
                                    ELSE 
                                         'Medio' 
                                    END
                               ELSE
                                    'Medio'
                               END
                          END
                     ELSE
                          CASE WHEN Tb1.TaxaFrequencia < tna.TFEmpQ3 THEN
                               CASE WHEN Tb1.TaxaGravidade > tna.TGEmpQ3 THEN 
                                    'Grave' 
                               ELSE 
                                    'Medio' 
                               END
                          ELSE
                               CASE WHEN Tb1.TaxaFrequenciaAnterior > tna.TFEmpCercaSuperior THEN  
                                    CASE WHEN Tb1.TaxaGravidade > tna.TGEmpQ1 THEN
                                         CASE WHEN Tb1.TaxaGravidadeAnterior > tna.TGEmpQ3 THEN 
                                              'Perigoso' 
                                         ELSE 
                                              'Grave' 
                                         END
                                    ELSE
                                         'Medio'
                                    END
                               ELSE
                                    CASE WHEN Tb1.TaxaFrequencia > tna.TGEmpQ2 THEN 
                                         'Grave' 
                                    ELSE 
                                         'Medio' 
                                    END
                               END
                          END
                     END
                END
           END AS NivelGravidade
    FROM
    (
        SELECT m.Num_Ano,
                funcEmpregEmpresaCount(m.Num_Ano, m.Cod_Empresa) AS EmpregadosProprios,
        
                (
          SELECT SUM(Num_Empr_Cont_Sep) + 
                 SUM(Num_Empr_Cont_Outros) AS Empregados
          FROM Mensal
          WHERE Sit_Mensal=4 AND Num_Ano=m.Num_Ano AND Cod_Empresa=m.Cod_Empresa
          GROUP BY Num_Mes 
          ORDER BY Num_Mes DESC LIMIT 0,1
        ) AS NumEmprCont,
        
        SUM(m.Num_HHER) AS HHER,
        
        SUM(m.Num_Tempo_Comput) AS TempoComputado,
        
                (
          SELECT COUNT(*) FROM Acidente a
          INNER JOIN Mensal ON (a.Cod_Mensal=Mensal.Cod_Mensal)
          WHERE Mensal.Num_Ano = m.Num_Ano AND Mensal.Cod_Empresa=m.Cod_Empresa
        ) AS AcidTipComAfast,
        
        SUM(m.Num_Acid_Tip_Sem_Afast) AS AcidTipSemAfast,
        
        COALESCE(
        (
                    SELECT COUNT(*) FROM Acidente a
          INNER JOIN Mensal ON (a.Cod_Mensal=Mensal.Cod_Mensal)
          WHERE Mensal.Sit_Mensal=4 AND Mensal.Num_Ano = m.Num_Ano AND 
                Mensal.Cod_Empresa=m.Cod_Empresa
        ) * (1000000 / SUM(m.Num_HHER)), 0) AS TaxaFrequencia,
        
                COALESCE(
        (
          SELECT
          (
                        SELECT COUNT(*) FROM Acidente a
            INNER JOIN Mensal ON (a.Cod_Mensal=Mensal.Cod_Mensal)
            WHERE Mensal.Sit_Mensal=4 AND Mensal.Num_Ano = mm.Num_Ano AND 
                  Mensal.Cod_Empresa=m.Cod_Empresa
          ) * (1000000 / SUM(mm.Num_HHER))
          FROM Mensal mm 
          WHERE mm.Sit_Mensal=4 AND mm.Num_Ano=(m.Num_Ano - 1) AND
                mm.Cod_Empresa=m.Cod_Empresa
                
        ), 0) AS TaxaFrequenciaAnterior,
        
        COALESCE(SUM(m.Num_Tempo_Comput) * (1000000 / SUM(m.Num_HHER)), 0) AS TaxaGravidade,
        
                COALESCE(
        (
          SELECT
             SUM(mm.Num_Tempo_Comput) * (1000000 / SUM(mm.Num_HHER))
          FROM Mensal mm 
          WHERE mm.Sit_Mensal=4 AND mm.Num_Ano=(m.Num_Ano - 1) AND
                mm.Cod_Empresa=m.Cod_Empresa
        ), 0) AS TaxaGravidadeAnterior,
        
                (SUM(m.Num_Acid_Tip_Leve_Cont_Sep) +  
         SUM(m.Num_Acid_Tip_Leve_Cont_Outros) +
         SUM(m.Num_Acid_Tip_Grav_Cont_Sep) + 
         SUM(m.Num_Acid_Tip_Grav_Cont_Outros)) AS AcidTipAfastCont,
         
                (         SUM(m.Num_Acid_Fat_Red_Cont_Sep) + 
         SUM(m.Num_Acid_Fat_Red_Cont_Outros) +
                  SUM(m.Num_Acid_Fat_Que_Cont_Sep) +
         SUM(m.Num_Acid_Fat_Que_Cont_Outros) +
                  SUM(m.Num_Acid_Fat_Vei_Cont) +
         SUM(m.Num_Acid_Fat_Out_Cont)) AS AcidFatCont,
         
                COALESCE(
        ((SUM(m.Num_Acid_Tip_Grav_Cont_Sep) + SUM(m.Num_Acid_Tip_Grav_Cont_Outros) +
            SUM(m.Num_Acid_Tip_Leve_Cont_Sep) + SUM(m.Num_Acid_Tip_Leve_Cont_Outros) +
            SUM(m.Num_Acid_Fat_Red_Cont_Sep) + SUM(m.Num_Acid_Fat_Red_Cont_Outros) + 
            SUM(m.Num_Acid_Fat_Que_Cont_Sep) + SUM(m.Num_Acid_Fat_Que_Cont_Outros) + 
            SUM(m.Num_Acid_Fat_Vei_Cont) + SUM(m.Num_Acid_Fat_Out_Cont)) 
          * 1000000) / (2000 * 
          (
            SELECT SUM(Num_Empr_Cont_Sep) + 
                   SUM(Num_Empr_Cont_Outros) AS Empregados
            FROM Mensal
            WHERE Sit_Mensal=4 AND Num_Ano=m.Num_Ano AND Cod_Empresa=m.Cod_Empresa
            GROUP BY Num_Mes 
            ORDER BY Num_Mes DESC LIMIT 0,1
          )), 0)
         AS TaxaFrequenciaCont,
        
        COALESCE(
        ((
                      ((SUM(m.Num_Acid_Fat_Red_Cont_Sep) + SUM(m.Num_Acid_Fat_Red_Cont_Outros) + 
             SUM(m.Num_Acid_Fat_Que_Cont_Sep) + SUM(m.Num_Acid_Fat_Que_Cont_Outros) + 
             SUM(m.Num_Acid_Fat_Vei_Cont) + SUM(m.Num_Acid_Fat_Out_Cont)) * 6000) +
                      ((SUM(m.Num_Acid_Tip_Grav_Cont_Sep) + SUM(m.Num_Acid_Tip_Grav_Cont_Outros)) * 500) +
                      ((SUM(m.Num_Acid_Tip_Leve_Cont_Sep) + SUM(m.Num_Acid_Tip_Leve_Cont_Outros)) * 30)
          ) * 1000000) / (2000 * 
            (
              SELECT SUM(Num_Empr_Cont_Sep) + 
                     SUM(Num_Empr_Cont_Outros) AS Empregados
              FROM Mensal
              WHERE Sit_Mensal=4 AND Num_Ano=m.Num_Ano AND Cod_Empresa=m.Cod_Empresa
              GROUP BY Num_Mes 
              ORDER BY Num_Mes DESC LIMIT 0,1
            )
        ), 0)
        AS TaxaGravidadeCont,
        
        (
          (
                        (SUM(m.Num_Acid_Tip_Leve_Cont_Sep) +  
             SUM(m.Num_Acid_Tip_Leve_Cont_Outros)) +
                         (SUM(m.Num_Acid_Tip_Grav_Cont_Sep) + 
             SUM(m.Num_Acid_Tip_Grav_Cont_Outros)) +
            (
                             SELECT COUNT(*) FROM Acidente a
                INNER JOIN Mensal ON (a.Cod_Mensal=Mensal.Cod_Mensal)
                WHERE  Mensal.Sit_Mensal=4 AND Mensal.Num_Ano=m.Num_Ano AND
                       Mensal.Cod_Empresa=m.Cod_Empresa
            )
          ) * 1000000 / (2000 * 
                    (
            SELECT SUM(Num_Empr_Cont_Sep) + 
                   SUM(Num_Empr_Cont_Outros) AS Empregados
            FROM Mensal
            WHERE Sit_Mensal=4 AND Num_Ano=m.Num_Ano AND Cod_Empresa=m.Cod_Empresa
            GROUP BY Num_Mes 
            ORDER BY Num_Mes DESC LIMIT 0,1
          ) + SUM(m.Num_HHER))
        ) AS TaxaFrequenciaForcTrab,
        
        (
          (
             (
               (
                                    ((                    SUM(m.Num_Acid_Fat_Red_Cont_Sep) + 
                    SUM(m.Num_Acid_Fat_Red_Cont_Outros) +
                                        SUM(m.Num_Acid_Fat_Que_Cont_Sep) +
                    SUM(m.Num_Acid_Fat_Que_Cont_Outros) +
                                        SUM(m.Num_Acid_Fat_Vei_Cont) +
                    SUM(m.Num_Acid_Fat_Out_Cont)
                   ) * 6000) +          
                                      (SUM(m.Num_Acid_Tip_Grav_Cont_Sep) + 
                    SUM(m.Num_Acid_Tip_Grav_Cont_Outros) * 500) +
                                      (SUM(m.Num_Acid_Tip_Leve_Cont_Sep) +  
                    SUM(m.Num_Acid_Tip_Leve_Cont_Outros) * 30)
                ) + 
                                SUM(m.Num_Tempo_Comput)
              ) * 1000000
            ) / 
            (2000 * 
                              (
                 SELECT SUM(Num_Empr_Cont_Sep) + 
                        SUM(Num_Empr_Cont_Outros) AS Empregados
                 FROM Mensal
                 WHERE Sit_Mensal=4 AND Num_Ano=m.Num_Ano AND Cod_Empresa=m.Cod_Empresa
                 GROUP BY Num_Mes 
                 ORDER BY Num_Mes DESC LIMIT 0,1
               ) + SUM(m.Num_HHER)
            )
        ) AS TaxaGravidadeForcTrab,
               
        (
         SELECT COUNT(*) FROM Populacao p
         INNER JOIN Mensal ON (p.Cod_Mensal=Mensal.Cod_Mensal)
         WHERE Mensal.Sit_Mensal=4 AND Mensal.Num_Ano=m.Num_Ano AND 
               Mensal.Cod_Empresa=m.Cod_Empresa AND
               p.Grav_Lesao = 'G'
        ) AS AcidGravePopulacao,
        
        (
         SELECT COUNT(*) FROM Populacao p
         INNER JOIN Mensal ON (p.Cod_Mensal=Mensal.Cod_Mensal)
         WHERE Mensal.Sit_Mensal=4 AND Mensal.Num_Ano=m.Num_Ano AND 
               Mensal.Cod_Empresa=m.Cod_Empresa AND
               p.Grav_Lesao = 'L'
        ) AS AcidLevePopulacao,
        
        (
         SELECT COUNT(*) FROM Populacao p
         INNER JOIN Mensal ON (p.Cod_Mensal=Mensal.Cod_Mensal)
         WHERE Mensal.Sit_Mensal=4 AND Mensal.Num_Ano=m.Num_Ano AND 
               Mensal.Cod_Empresa=m.Cod_Empresa AND
               p.Grav_Lesao = 'F'
        ) AS AcidFatalPopulacao,
        
        AVG(Num_Medio_Hab_Pop) AS NumMedioHabPop
                                                                                                                   
        FROM Mensal m
        WHERE m.Sit_Mensal=4 AND m.Cod_Empresa=CodEmpresa AND m.Num_Ano>=AnoInicio AND m.Num_Ano<=AnoFim
        GROUP BY m.Num_Ano, m.Cod_Empresa
    ) AS Tb1 
     LEFT JOIN TmpNivelAcids tna ON(Tb1.Num_Ano=tna.Num_Ano);

END;

Editado por Denis Courcy
Melhorar entendimento do código
Link para o comentário
Compartilhar em outros sites

4 respostass a esta questão

Posts Recomendados

  • 0

Oi, 'Leonardo Viana'

Olhe os trechos de seu código, abaixo:

...
        CREATE TEMPORARY TABLE IF NOT EXISTS TmpNivelAcids
...


    DELETE FROM TmpNivelAcids;
    
...
            INSERT INTO TmpNivelAcids

como se insere dados em uma tabela que não existe?

Link para o comentário
Compartilhar em outros sites

  • 0
Oi, 'Leonardo Viana'

Olhe os trechos de seu código, abaixo:

...
        CREATE TEMPORARY TABLE IF NOT EXISTS TmpNivelAcids
...


    DELETE FROM TmpNivelAcids;
    
...
            INSERT INTO TmpNivelAcids

como se insere dados em uma tabela que não existe?

so estou limpando a tabela

Link para o comentário
Compartilhar em outros sites

Visitante
Este tópico está impedido de receber novos posts.


  • Estatísticas dos Fóruns

    • Tópicos
      152,3k
    • Posts
      652,4k
×
×
  • Criar Novo...