[SOLVED] Select sequence number not allowed here

Select sequence number not allowed here

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?

Solution

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