Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table ss (nsample, lsl, usl, failed, dimension_id, sample_date) as
select 1, 2, 3, 'Y', 1, trunc(sysdate, 'HH') - interval '75' minute from dual
union all
select 2, 3, 4, 'Y', 2, trunc(sysdate, 'HH') - interval '15' minute from dual
union all
select 3, 4, 5, 'Y', 3, trunc(sysdate, 'HH') + interval '15' minute from dual;
3 rows affected

create table sd (dimension_id) as
select level from dual connect by level <= 5;
5 rows affected

-- dummy code
select *
from ss, sd -- but please use ANSI joins!
-- your code
WHERE ((SS.NSAMPLE < SS.LSL OR SS.NSAMPLE > SS.USL) OR SS.FAILED = 'Y')
AND SD.DIMENSION_ID = SS.DIMENSION_ID
AND SUBDATE(STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H'), INTERVAL 1 HOUR) <= SS.SAMPLE_DATE AND SS.SAMPLE_DATE < STR_TO_DATE(DATE_FORMAT(NOW(), '%Y%m%d%H'), '%Y%m%d%H')
ORA-00907: missing right parenthesis

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
select sysdate, trunc(sysdate, 'HH'), trunc(sysdate, 'HH') - interval '1' hour
from dual


SYSDATE
TRUNC(SYSDATE,'HH')
TRUNC(SYSDATE,'HH')-INTERVAL'1'HOUR


2022-12-21 17:30:37
2022-12-21 17:00:00
2022-12-21 16:00:00


-- dummy code
select *
from ss, sd -- but please use ANSI joins!
-- your code
WHERE ((SS.NSAMPLE < SS.LSL OR SS.NSAMPLE > SS.USL) OR SS.FAILED = 'Y')
AND SD.DIMENSION_ID = SS.DIMENSION_ID
-- changed code
AND SS.SAMPLE_DATE >= TRUNC(SYSDATE, 'HH') - INTERVAL '1' HOUR
AND SS.SAMPLE_DATE < TRUNC(SYSDATE, 'HH')


NSAMPLE
LSL
USL
FAILED
DIMENSION_ID
SAMPLE_DATE
DIMENSION_ID


2
3
4
Y
2
2022-12-21 16:45:00
2


SELECT *
FROM SS
JOIN SD ON SD.DIMENSION_ID = SS.DIMENSION_ID
WHERE ((SS.NSAMPLE < SS.LSL OR SS.NSAMPLE > SS.USL) OR SS.FAILED = 'Y')
AND SS.SAMPLE_DATE >= TRUNC(SYSDATE, 'HH') - INTERVAL '1' HOUR
AND SS.SAMPLE_DATE < TRUNC(SYSDATE, 'HH')


NSAMPLE
LSL
USL
FAILED
DIMENSION_ID
SAMPLE_DATE
DIMENSION_ID


2
3
4
Y
2
2022-12-21 16:45:00
2