By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE camion ( nro_patente, valor_arriendo_dia, valor_garantia_dia ) AS
SELECT 1, 1, 1 FROM DUAL;
1 rows affected
CREATE TABLE arriendo_camion ( id_arriendo, nro_patente, fecha_ini_arriendo ) AS
SELECT 1, 1, SYSDATE FROM DUAL;
1 rows affected
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;
| ROWNUM | ANNO_PROCESO | NRO_PATENTE | Arriendos |
|---|---|---|---|
| 1 | 2019 | 1 | 1 |
CREATE SEQUENCE SEQ_ARRIENDO;
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;
| NEXTVAL | ANNO_PROCESO | NRO_PATENTE | Arriendos |
|---|---|---|---|
| 1 | 2019 | 1 | 1 |
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;
| NEXTVAL | ANNO_PROCESO | NRO_PATENTE | Arriendos |
|---|---|---|---|
| 2 | 2019 | 1 | 1 |