This discussion is archived
5 Replies Latest reply: Nov 15, 2012 10:29 AM by rp0428 RSS

error ORA-00904  invalid identifier

Jimmie_M Newbie
Currently Being Moderated
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points