Date math help with 8.0.5

From: Steven Cardinal <scardinal_at_nospamyahoo.com>
Date: 2000/02/16
Message-ID: <88epv3$k51@journal.concentric.net>#1/1

Hello,

I'm trying to run a query (actually a view once I get the query to work) that will pull data based on some date parameters, ie :

Show me all users whose STARTTIME < SYSDATE and ENDTIME > SYSDATE   AND this hour needs to be based on an assigned frequency. Say they are a valid record every 2 hours between the starttime and endtime. I've tried the following query, but it doesn't always work:

select R.USERID, U.EMAIL, U.FNAME, D.DRUGNAME, D.MESSAGE from EPROMPT_USER U, EPROMPT_DRUG D, EPROMPT_RX R where U.USERID = R.USERID

and D.DRUGID = R.DRUGID
and R.STARTTIME < (SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL)
and R.ENDTIME > (SELECT TO_CHAR(SYSDATE, 'HH24') FROM DUAL)

and 0 = (SELECT MOD((TO_CHAR(SYSDATE, 'HH24'))-R.FREQUENCY, R.STARTTIME) FROM DUAL) My starttime, endtime and frequency are Numbers (0-23, 0-23 and 1-8 respectively) and I'm formatting SYSDATE as HH24. I get data if SYSTIME is 12:xx noon-ish. It's now 1:00 and I get no records, based on my data I should get 2 records.

If I reverse the last AND to
and = (SELECT MOD((TO_CHAR(SYSDATE, 'HH24'))-R.FREQUENCY, R.STARTTIME) FROM DUAL) = 0 I get ORA-00936: missing expression

Any ideas? I'm new to Oracle and can't find much info in any of my Oracle books (SQL*Plus, DBA handbook, Complete Reference or DBA guide)

Thanks
Steve Received on Wed Feb 16 2000 - 00:00:00 CST