Styjun

I understand that exists a lot of material about this, but I still cannot make this work properly. The code above is my last attempt. The problem is I have duplicate and sometimes triplicate records in a table

What I need:

• get repeated records


• "save" the minor sequencial of each repeated record (first to be created, should be maintained)


• delete other records

*I could not make group by... having count > 1 using sequential, cause sequentials do not repeat

DECLARE

TYPE SEQ_HISTORICO IS TABLE OF SIA.HISTORICO_ESCOLAR.NUM_SEQ_HISTORICO%TYPE;

V_MANTER SEQ_HISTORICO; -- least sequential, should be mantained

V_EXCLUIR SEQ_HISTORICO; -- others, to be deleted


BEGIN

 WITH 
 DUPLICADAS AS
 (
 select he.seq_historico,
 he.cod_disciplina,
 di.nom_disciplina,
 pa.nom_fantasia,
 he.cod_situacao_aluno_turma
 from ow.historico_escolar he
 inner join ow.periodo_academico pa on he.num_seq_periodo_academico =
 pa.num_seq_periodo_academico
 inner join ow.disciplina di on he.cod_disciplina = di.cod_disciplina
 where he.seq_aluno_curso = '424242'
 and he.cod_situacao_aluno_turma = 'IS'

 )
 SELECT LEAST(nseq_historico) INTO V_MANTER FROM DUPLICADAS;

END;

ERROR: pls-00642 local collection types not allowed in sql statement

Should I use pls_integer?

The error is trying to tell you that you can't SELECT...INTO... an instance of a type declared in PL/SQL. In this case the problem arises because of

SELECT LEAST(nseq_historico) INTO V_MANTER FROM DUPLICADAS

Here, V_MANTER is of type SEQ_HISTORICO which is defined in a PL/SQL block.

You can, however, BULK COLLECT into a PL/SQL collection, but in order to use the MIN function you'll need to add a GROUP BY clause. It appears from your comments that you want to group by HISTORICO_ESCOLAR.COD_DISCIPLINA, so putting this together we get:

WITH DUPLICADAS AS (select he.seq_historico,
 he.cod_disciplina,
 di.nom_disciplina,
 pa.nom_fantasia,
 he.cod_situacao_aluno_turma
 from ow.historico_escolar he
 inner join ow.periodo_academico pa
 on he.num_seq_periodo_academico = pa.num_seq_periodo_academico
 inner join ow.disciplina di
 on he.cod_disciplina = di.cod_disciplina
 where he.seq_aluno_curso = '424242' and 
 he.cod_situacao_aluno_turma = 'IS')
SELECT COD_DISCIPLINA, MIN(SEQ_HISTORICO) 
 BULK COLLECT INTO V_MANTER
 FROM DUPLICADAS
 GROUP BY COD_DISCIPLINA

Best of luck.