Andrew Raibeck posted on this problem a while back. I don't remember
exactly where to find that post, but I remember the problem is not your
WHERE, it has something to do with the fact that the EVENTS table is
unlike other tables.
It works if you put in a constant date, as you found. So you can fool
it by using a combination of bogus constants and relative timestamps,
like this:
select schedule_name, date(scheduled_start), status from events where
scheduled_start between '2003-12-01' and '2020-12-31' and
scheduled_start >= current_timestamp-24 hours
-----Original Message-----
From: ADSM: Dist Stor Manager [mailto:ADSM-L AT VM.MARIST DOT EDU] On Behalf Of
David Moore
Sent: Thursday, January 20, 2005 11:05 AM
To: ADSM-L AT VM.MARIST DOT EDU
Subject: Scripting question
Hello List -
My goal is to script a customized report in the Operational Reporting
tool, to report backup information for selected nodes to specific email
recipients (the nodes are differentiated by the node-contact field).
My problem is that a straight select on the EVENTS table is only pulling
back results that occurred since midnight. Then, when I try to insert a
WHERE clause (WHERE SCHEDULED_START >= CURRENT_TIMESTAMP - 24 HOURS), I
get a RC=11 (no match). With trial & error, if I hard-code the previous
day (WHERE SCHEDULED_START >= '2005-01-19 17:00'), I receive the results
I'm looking for. But, I need this in script form with no hard-coding,
since it will run automatically.
I've also investigated the use of a Q EVENT * * clause, but I can't use
it, because I can't key on the CONTACT field off the NODES table (I
could hard-code the specific nodes, but that changes relatively often,
so that's not a fool-proof method for my site).
Does anyone have a solution to this problem, either through the use of a
variable or a TIME field that will compare to the SCHEDULED_START field?
Remember, it needs to be a straight SELECT statement that runs through
the Operational Reporting tool.
Thanks, in advance and have a happy Mardi Gras.
David Moore
State of Louisiana
|