5 Replies Latest reply: Nov 15, 2012 12:29 PM by rp0428 RSS

    error ORA-00904  invalid identifier

    Jimmie_M
      There are so many different employee rates colums in the employee table .
      e.g)
      emp_rate_1 column has all different salaries(10,20,30)
      emp_rate_2 column has all different salaries(20,30,40)


      If I do pick up one rate , it works fine as below
      select
           SUM(emp_rate_1) as emp_rate_1
                     from employee_t

      result $60

      but i have to summarize all different rate amount using loop. before looping method,
      I tried as simple like below

      declare
      V_emp_rate_1_ID number;
      begin
      V_emp_rate_1_ID :=1;
      select

           SUM(emp_rate_||trim(V_emp_rate_1_ID)) as emp_rate_1
                     from employee_t ;
      end;

      I have an error message;

      PL/SQL: ORA-00904: "emp_rate_": invalid identifier
      ORA-06550: line 5, column 1:
      PL/SQL: SQL Statement ignored
      06550. 00000 - "line %s, column %s:\n%s"
      *Cause:    Usually a PL/SQL compilation error.
      *Action:


      how can I put dynamic column into Sum() in the query?
      or would you give me your advice to solve the solution.