ORACLE stored procedure doesn't compile

Question

I'm struggling on this query, and can't figure out why Oracle doesn't compile the following Stored Procedure, the error is PL/SQL: ORA-00933: comando SQL terminato erroneamente, on the line containing `FROM storico_dipendente AS anag,

Thanks in advance

Paolo`

create or replace 
    PROCEDURE AVERAGE_VRE (
                p_refDate IN DATE ,
          p_level IN VARCHAR2 )
    /*
        Procedure Name: AVERAGE_VRE
        Author:         Paolo URSINI
        Date:           21 jan 2015
        Description:    this procedure compute the average amount from PAYROLL table
                        inserting into PAYROLL_CALCOLATO table.
                        The assumption is that every wage type is equally divided
                        by ALL THE ACTIVE EMPLOYEE
    */
    IS 
      FTE decimal(8,3) := 0;

    BEGIN    
      SELECT sum(percentuale_parttime) into FTE
        from storico_dipendente
        where TRUNC(data_inizio_variazione) <= p_refDate 
            and TRUNC(data_fine_variazione) >= p_refDate 
            and flag_status = 1;    

        INSERT INTO PAYROLL_CALCOLATO (
            MATRICOLA, 
            DATA_PY, 
            IMPORTO,
            ID_CODICE_VOCE,
            TM_INS,
            TM_UPD,
            ID_GRUPPO_DATI)
            SELECT 
                anag.MATRICOLA, voci.DATA_PY, (voci.sumImporto / FTE ), 
                voci.ID_CODICE_VOCE, 
                SYSDATE, 
                SYSDATE, 
                2           
            FROM storico_dipendente AS anag,
          (
            SELECT DATA_PY, ID_CODICE_VOCE, sum(IMPORTO_PY) sumImporto
              FROM PAYROLL 
              WHERE TRUNC(DATA_PY) = p_refDate
              GROUP BY DATA_PY, ID_CODICE_VOCE
          ) AS voci         
        where TRUNC(data_inizio_variazione) <= p_refDate 
            and TRUNC(data_fine_variazione) >= p_refDate 
            and flag_status = 1 and cod_livello = p_level;  

    END AVERAGE_VRE;

Boneist · Accepted Answer · 2015-01-22 17:29:21Z

3

Remove the "AS" from the two table/subquery aliases. Whilst Oracle allows you to use AS with column aliases, it's not valid syntax for table aliases.

answered Jan 22, 2015 at 17:29

Boneist

23.6k1 gold badge28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paolo Ursini Over a year ago

Thank you, found the same solution

Paolo Ursini · Accepted Answer · 2015-01-22 17:56:22Z

SOLVED, the problem was in the alias with the keyword AS. The following code compiles correctly

 CREATE OR REPLACE PROCEDURE AVERAGE_VRE (
            p_refDate IN DATE ,
      p_level IN VARCHAR2 )
/*
    Procedure Name: AVERAGE_VRE
    Author:         Paolo URSINI
    Date:           21 jan 2015
    Description:    this procedure compute the average amount from PAYROLL table
                    inserting into PAYROLL_CALCOLATO table.
                    The assumption is that every wage type is equally divided
                    by ALL THE ACTIVE EMPLOYEE
*/
IS 
  FTE decimal(8,3) := 0;

BEGIN    
  SELECT sum(percentuale_parttime) into FTE
    from storico_dipendente
    where TRUNC(data_inizio_variazione) <= p_refDate 
        and TRUNC(data_fine_variazione) >= p_refDate 
        and flag_status = 1 and cod_livello = p_level;    

    INSERT INTO PAYROLL_CALCOLATO (
        MATRICOLA , 
        DATA_PY, 
        IMPORTO,
        ID_CODICE_VOCE,
        TM_INS,
        TM_UPD,
        ID_GRUPPO_DATI)
        SELECT 
            anag.MATRICOLA ,voci.DATA_PY, (voci.sumImporto / FTE ), 
            voci.ID_CODICE_VOCE, 
            SYSDATE, 
            SYSDATE, 
            2         
        FROM storico_dipendente anag,
              (
                SELECT DATA_PY, ID_CODICE_VOCE, sum(IMPORTO_PY) sumImporto
                  FROM PAYROLL 
                  WHERE TRUNC(DATA_PY) = p_refDate
                  GROUP BY DATA_PY, ID_CODICE_VOCE
              ) voci            
    where TRUNC(data_inizio_variazione) <= p_refDate 
        and TRUNC(data_fine_variazione) >= p_refDate 
        and flag_status = 1 and cod_livello = p_level;  

END AVERAGE_VRE;

Collectives™ on Stack Overflow

ORACLE stored procedure doesn't compile

2 Answers 2

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Related