SELECT INSERT in Oracle using Sesquence nextval and group by

Question

Good morning, you guys!

I’m trying to make an Oracle select using a SEQUENCE in id, but is giving error on account of a group by no select.

Dry up my SQL:

  INSERT INTO SUP_T(ID, DESCRICAO)

  SELECT SEQ_SUP_T.NEXTVAL,TIPO.TIPO
  FROM TB_TIPO_APLICACAO TIPO
  GROUP BY TIPO.TIPO;

The error that appears is as follows:

Relatório de erros -
Erro de SQL: ORA-02287: número de seqüência não permitido aqui
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Someone’s been through it?

Answer 1

Oracle does not allow you to use group by com Quence so you have to group first then include Quence. You can do as much with group by as with distinct.

group by:

INSERT INTO SUP_T(ID, DESCRICAO)
  SELECT SEQ_SUP_T.NEXTVAL, T.TIPO
    FROM(SELECT TIPO.TIPO FROM TB_TIPO_APLICACAO TIPO group by TIPO.TIPO) T

distinct:

INSERT INTO SUP_T(ID, DESCRICAO)
  SELECT SEQ_SUP_T.NEXTVAL, T.TIPO
    FROM(SELECT DISTINCT TIPO.TIPO FROM TB_TIPO_APLICACAO TIPO) T

hope I’ve helped.

Answer 2

Try to query the query in a sub-query, this way.

The sequence query should always occur on the DUAL object in Oracle.

Edited: I believe this is the query that will result in what you are waiting for with GROUP BY, considering that no group clauses are used on the return of your SELECT (Min, Max, Count, Sum) and also no HAVING condition.

Follows modified insert:

INSERT INTO SUP_T(ID, DESCRICAO)

SELECT (SELECT SEQ_SUP_T.NEXTVAL FROM DUAL) As SEQUENCE, 
       T.TIPO
FROM
(
   SELECT DISTINCT TIPO.TIPO
   FROM TB_TIPO_APLICACAO TIPO
) T