RATE THIS ANSWER
+1
Click to Vote:
- 1
0
Hello Firasha.
You asked the same question a few days ago. If the answer provided was not useful, it would have been better to continue the discussion in the
original question instead of creating a new one.
You are getting the INVALID NUMBER error because you are trying to perform an arithmetic operation between two varchar variables.
To calculate time differences you need to have your time values in DATE columns, there is no need to convert them to varchars.
Example:
SELECT sysdate - complaint_log_time FROM your_table;
If you want the result in hours, just convert it:
SELECT (sysdate - complaint_log_time)*24 FROM your_table;
To check whether the complaint was resolved at the time limit, you would need an application to keep monitoring the time limits for each complaint, or you could schedule a job, using the
DBMS_JOB database package.
A trigger won't work because triggers fire when something happens in the database, not when something does not happen.
As for how to send the e-mail, a couple of links were provided in the answer to the
original question.
Last Answered:
May 22 2009 2:15 PM GMT by Carlosdl 
29855 pts.
