CODEWITHSUNDEEP
oracle-database
Harold S Rpo
Harold S Rpo

Reputation: 1

I need comparate this column with a variable for update a table

I need comparate this column with a variable for update a table, i have this query:

declare v_IdMig int;
begin
select max(ID_Mig)
      into v_IdMig
      from MIGPT021_LOG;

update MIGPT021_LOG
set RegPostMig = (select sum(cantidad) CantidadReg
from
(
select count(*) cantidad, sysdate FechaHora from CRPDTA.F59PT021
union all
select count(*) cantidad, sysdate FechaHora from CRPDTA.F59PT021_HTR)),
FecPostReg = (select max(FechaHora) Fecha
from
(
select count(*) cantidad, sysdate FechaHora from CRPDTA.F59PT021
union all
select count(*) cantidad, sysdate FechaHora from CRPDTA.F59PT021_HTR))
where max(Id_Mig) = v_IdMig;

But i have this error:

ORA-06550: línea 20, columna 7: PL/SQL: ORA-00934: función de grupo no permitida aquí ORA-06550: línea 7, columna 1: PL/SQL: SQL Statement ignored ORA-06550: línea 20, columna 28: PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with <a double-quote 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action:

Thank you

Upvotes: 0

Views: 33

Answers (1)

Dave Costa
Dave Costa

Reputation: 48131

ORA-00934: función de grupo no permitida aquí

You cannot use a group function (such as MAX) in the WHERE clause, as you are trying to do here:

where max(Id_Mig) = v_IdMig;

I think you simply want:

where Id_Mig = v_IdMig;

You've selected the maximum value already. I presume you then want to update the row that has that value.

Upvotes: 1

Related Questions