SELECT INSERT in Oracle using sequence nextval and group by

Navigation
1

Good morning, guys!

I'm trying to do a select insert in Oracle using a SEQUENCE in the id, but it's giving error on account of a group by in select.

Dry 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.

Has anyone ever been through this?

    
asked by anonymous 04.07.2016 / 13:59

2 answers

2

Oracle does not allow you to use group by with sequence so you have to do the grouping first then include the sequence. It can do both with group by and 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

I hope I have helped.

    
14.07.2016 / 20:08
0

Attempts to query the sequence in a sub-query, in this way.

The query in sequences must always occur in the DUAL object in Oracle.

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

Following changed 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
    
04.07.2016 / 14:02
Display the vector values in c ++ .OrderBy dynamically in Lambda