CODEWITHSUNDEEP
sqloracle-database
RicardoBarros
RicardoBarros

Reputation: 163

code in sql does not meet the AND condition

What I want is that if the day in date_prestamo is 6 and the day in return date is 6 then do not show that record. But the filter in the commented condition is not doing it.

--no funciona LA CONDICION COMENTADA
SELECT e.ID_LECTOR,e.NOMBRE, p.FECHA_PRESTAMO, p.FECHA_DEVOLUCION
FROM ESTUDIANTE e
JOIN PRESTAMO p
ON e.ID_LECTOR = p.ID_LECTOR
GROUP BY e.ID_LECTOR,e.NOMBRE, p.FECHA_PRESTAMO, p.FECHA_DEVOLUCION
--HAVING ((EXTRACT(DAY FROM  p.FECHA_PRESTAMO) <> 06)
--AND (EXTRACT(DAY FROM  p.FECHA_DEVOLUCION) <> 06))
ORDER BY ID_LECTOR;

Without HAVING ((EXTRACT...... it selects:

19044429-9  Erato   16/06/17    21/06/17
19044429-9  Erato   18/09/17    24/09/17
19044429-9  Erato   11/04/17    15/04/17
19044429-9  Erato   03/10/17    06/10/17

With HAVING ((EXTRACT..... it selects:

19044429-9  Erato   18/09/17    24/09/17
19044429-9  Erato   11/04/17    15/04/17
19044429-9  Erato   16/06/17    21/06/17

Upvotes: 1

Views: 75

Answers (3)

EoinS
EoinS

Reputation: 5482

I think its your NLS settings

Use this to get day #, without changing settings:

1 + TRUNC (date) - TRUNC (date, 'IW'). --<>6 in your case

Similar SO

Oracle community

Copy and paste this query to see if it resolves your issue:

SELECT e.ID_LECTOR,e.NOMBRE, p.FECHA_PRESTAMO, p.FECHA_DEVOLUCION
FROM ESTUDIANTE e
JOIN PRESTAMO p
ON e.ID_LECTOR = p.ID_LECTOR
GROUP BY e.ID_LECTOR,e.NOMBRE, p.FECHA_PRESTAMO, p.FECHA_DEVOLUCION
HAVING ( 1 + TRUNC (p.FECHA_PRESTAMO) - TRUNC (p.FECHA_PRESTAMO, 'IW') ) <> 06)
AND  ( 1 + TRUNC (p.FECHA_DEVOLUCION) - TRUNC (p.FECHA_DEVOLUCION, 'IW') ) <> 06)
ORDER BY ID_LECTOR;

Upvotes: 1

APC
APC

Reputation: 146239

"I want is that if the day in "date_prestamo" is 6 and the day in "return date" is 6 then do not show that record"

It's a problem in Boolean logic. The effect of a condition NOT x AND NOT y is to exclude records where either x or y is true. You need to use an OR in your condition.

You should use a WHERE clause to filter rather than HAVING.

select e.id_lector,e.nombre, p.fecha_prestamo, p.fecha_devolucion
from estudiante e
join prestamo p
on e.id_lector = p.id_lector
where ((extract(day from  p.fecha_prestamo) <> 06)
or (extract(day from  p.fecha_devolucion) <> 06))
order by e.id_lector, p.fecha_prestamo;

This works, and I have written an Oracle LiveSQL demo to prove it. Check it out.

Upvotes: 1

Ferdinand Gaspar
Ferdinand Gaspar

Reputation: 2063

That can be achieved by having your condition in WHERE clause

SELECT e.ID_LECTOR,
       e.NOMBRE, 
       p.FECHA_PRESTAMO, 
       p.FECHA_DEVOLUCION
  FROM ESTUDIANTE e
  JOIN PRESTAMO p
    ON e.ID_LECTOR = p.ID_LECTOR
 WHERE EXTRACT(DAY FROM  p.FECHA_PRESTAMO) <> 06
   AND EXTRACT(DAY FROM  p.FECHA_DEVOLUCION) <> 06)
 ORDER BY e.ID_LECTOR;

Upvotes: 0

Related Questions