Select sequence number not allowed here

Question

I´ve a problem than i can´t resolve a select with a sequence, here´s my query

SELECT  SEQ_ARRIENDO.nextval,
        TO_CHAR(SYSDATE,'YYYY') ANNO_PROCESO,
        cam.nro_patente,
        ( SELECT COUNT(ac.id_arriendo)
          FROM   arriendo_camion ac
          where  cam.nro_patente = ac.nro_patente
          and    TO_CHAR(ac.fecha_ini_arriendo,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
          having count(ac.id_arriendo) < 4
        ) "Arriendos"
FROM    camion CAM--, arriendo_camion ac
where   ( SELECT COUNT(ac.id_arriendo)
          FROM   arriendo_camion ac
          where  cam.nro_patente = ac.nro_patente 
          and    TO_CHAR(ac.fecha_ini_arriendo,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
          having count(ac.id_arriendo) < 4
        ) is not null
GROUP BY cam.nro_patente,
        cam.valor_arriendo_dia,
        cam.valor_garantia_dia
order by cam.nro_patente;

Any ideas?

Possibly relevant: https://stackoverflow.com/questions/34546009/ora-02287-sequence-number-not-allowed-here — Dave Costa, Dec 09 '19 at 15:01
Please **edit your question** using the `edit` button just below the question tags and include the errors you're getting when you run this statement. Thanks. — Bob Jarvis - Слава Україні, Dec 09 '19 at 15:49

MT0 · Accepted Answer · 2019-12-09T15:11:48.543

If you use a sequence then the first time you perform the query you will generate values; then the next time the query is executed you will not get the same values but will get the next values in the sequence. This is probably not what you are after.

Oracle Setup:

CREATE TABLE camion ( nro_patente, valor_arriendo_dia, valor_garantia_dia ) AS
SELECT 1, 1, 1 FROM DUAL;

CREATE TABLE arriendo_camion ( id_arriendo, nro_patente, fecha_ini_arriendo ) AS
SELECT 1, 1, SYSDATE FROM DUAL;

CREATE SEQUENCE SEQ_ARRIENDO;

Query with Sequence:

SELECT  SEQ_ARRIENDO.NEXTVAL,
        t.*
FROM    (
  SELECT  TO_CHAR(SYSDATE,'YYYY') ANNO_PROCESO,
          cam.nro_patente,
          ( SELECT COUNT(ac.id_arriendo)
            FROM   arriendo_camion ac
            where  cam.nro_patente = ac.nro_patente
            and    TO_CHAR(ac.fecha_ini_arriendo,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
            having count(ac.id_arriendo) < 4
          ) "Arriendos"
  FROM    camion CAM
  GROUP BY cam.nro_patente,
          cam.valor_arriendo_dia,
          cam.valor_garantia_dia
  order by cam.nro_patente
) t
where   "Arriendos" is not null;

Output:

The first time you run the query you will get:

ROWNUM | ANNO_PROCESO | NRO_PATENTE | Arriendos
-----: | :----------- | ----------: | --------:
     1 | 2019         |           1 |         1

The second time you run the same query, you will get:

NEXTVAL | ANNO_PROCESO | NRO_PATENTE | Arriendos
------: | :----------- | ----------: | --------:
      2 | 2019         |           1 |         1

And the sequence number will keep going up starting from the increment from the previous NEXTVAL.

Query with ROWNUM:

Assuming you just want an incrementing integer value starting from 1 then order your query and then use ROWNUM:

SELECT  ROWNUM,
        t.*
FROM    (
  SELECT  TO_CHAR(SYSDATE,'YYYY') ANNO_PROCESO,
          cam.nro_patente,
          ( SELECT COUNT(ac.id_arriendo)
            FROM   arriendo_camion ac
            where  cam.nro_patente = ac.nro_patente
            and    TO_CHAR(ac.fecha_ini_arriendo,'YYYY') = TO_CHAR(SYSDATE,'YYYY') 
            having count(ac.id_arriendo) < 4
          ) "Arriendos"
  FROM    camion CAM
  GROUP BY cam.nro_patente,
          cam.valor_arriendo_dia,
          cam.valor_garantia_dia
  order by cam.nro_patente
) t
where   "Arriendos" is not null;

Output:

This will always start the "sequence" at 1:

ROWNUM | ANNO_PROCESO | NRO_PATENTE | Arriendos
-----: | :----------- | ----------: | --------:
     1 | 2019         |           1 |         1

db<>fiddle here

Thank u so much, otherwise, can u explain why o how use the "t*". — Héctor Mancilla, Dec 09 '19 at 16:44
The inner query has been given the alias `t` and `t.*` is just used to select all the columns generated in the inner query. — MT0, Dec 09 '19 at 18:22

score 1 · Answer 2 · answered Dec 09 '19 at 15:00

That's a documented restriction:

Restrictions on Sequence Values

You cannot use CURRVAL and NEXTVAL in the following constructs:

A subquery in a DELETE, SELECT, or UPDATE statement

A query of a view or of a materialized view

A SELECT statement with the DISTINCT operator

A SELECT statement with a GROUP BY clause or ORDER BY clause

A SELECT statement that is combined with another SELECT statement with the UNION, INTERSECT, or MINUS set operator

The WHERE clause of a SELECT statement

The condition of a CHECK constraint

score 0 · Answer 3 · answered Dec 09 '19 at 15:00

Can't test it...

with t_arriendos as
(
    select 
        count(id_arriendo) count_id_arriendo,
        nro_patente nro_patente
    from arriendo_camion
    where to_char(fecha_ini_arriendo,'YYYY')= to_char(sysdate,'YYYY') 
    group by nro_patente
    having count(id_arriendo) <4
)
select
    seq_arriendo.nextval        sequence,
    to_char(sysdate,'YYYY')     anno_proceso,
    cam.nro_patente             patente,
    ac.count_id_arriendo           Arriendos
from camion cam
join t_arriendos ac
    on cam.nro_patente = ac.nro_patente
order by
    cam.nro_patente;

score 0 · Answer 4 · answered Dec 09 '19 at 15:40

if you realy need to use the seq cause some reason that cant adopt the row_num way presented in MTO's answer.

you should try something like this

select 
   SEQ_ARRIENDO.nextval,
   TO_CHAR(SYSDATE,'YYYY') ANNO_PROCESO,
   cam.nro_patente,
   VAC.count_arriendos
from (
   SELECT ac.nro_patente, COUNT(ac.id_arriendo) count_arriendos
   FROM   arriendo_camion ac
   where  TO_CHAR(ac.fecha_ini_arriendo,'YYYY') = TO_CHAR(SYSDATE,'YYYY')
   group by ac.nro_patente
   having count(ac.id_arriendo) < 4
) VAC
inner join camion CAM
on cam.nro_patente = VAC.nro_patente

see fiddle

https://dbfiddle.uk/?rdbms=oracle_11.2&fiddle=3baeef28187e0a14a8b9cf04047996a0

Select sequence number not allowed here

4 Answers4