I am trying to run the following query with a sysdate of May 9. How do I do that?



declare
v_start_date date;
v_end_date date;
v_ralpha number;
v_ralpha_no_result number;
v_rcr_searching number;
v_rcr_not_searching number;
v_rcategory number;
v_rcategory_no_result number;
v_rbad_category number;
v_total_cr number;

begin
select (sysdate-1) into v_start_date from dual;
select (sysdate) into v_end_date from dual;

delete pdt_info;

SELECT COUNT(se_type_code) into v_ralpha
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty <> 0
AND se_type_code = 1
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

SELECT COUNT(se_type_code) into v_ralpha_no_result
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty = 0
AND se_type_code = 1
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

insert into pdt_info
select distinct caller_rec_no
from tinfo_req_hist
where info_req_start_time between v_start_date and v_end_date
and caller_rec_no not in (9999, 8888);


SELECT COUNT(*) into v_rcr_searching
FROM pdt_info;

SELECT COUNT(caller_rec_no) into v_total_cr
FROM tcaller_rec
WHERE start_time between v_start_date and v_end_date;

v_rcr_not_searching:= v_total_cr - v_rcr_searching;

SELECT COUNT(se_type_code) into v_rcategory
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty <> 0
AND se_type_code in (105, 110)
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

If you are trying to get information for the last 24 hours, you might
try trunc(sysdate) ... sysdate is actually in the form 9-MAY-2004 HH:MMS
(depending on your nls_date settings ...)

The startdate that you are loading into actually contains the hours, mins, secs ...

HTH
Gregg

gbrabham,

Perhaps I didn't ask the question properly.
The script below represents a script that is run daily.
At this point we need to rerun it for May 9, 2004.
Well, today is the 20th.
What do I put in place of the Sysdate -1?

to_date('9-MAY-2004','DD-MON-YYYY')
(to_date('9-MAY-2004','DD-MON-YYYY'))-1

HTH
Gregg

So what you are saying is:
I should put it like this:

begin
select to_date('9-MAY-2004','DD-MON-YYYY') into v_start_date from dual;
select (to_date('9-MAY-2004','DD-MON-YYYY'))-1 into v_end_date from dual;

Correct ...

gbrabham,


This does not want to play nicely. I am getting ora-01830 errors.
begin
select to_date('9-MAY-2004','DD-MON-YYYY') into v_start_date from dual;
select (to_date('9-MAY-2004','DD-MON-YYYY'))-1 into v_end_date from dual;

why not change:
AND info_req_start_time between v_start_date and v_end_date;

to something like this:
and to_char(info_req_start_time, 'DD-MON-YYYY') = '09-MAY-2004'

that way you can even avoid selecting from dual

>This does not want to play nicely. I am getting ora-01830 errors.
>begin
>select to_date('9-MAY-2004','DD-MON-YYYY') into v_start_date from dual;
>select (to_date('9-MAY-2004','DD-MON-YYYY'))-1 into v_end_date from dual;
Of course Oracle complains, you specified DD and only provided "9"
v_start_date := to_date('09-MAY-2004','DD-MON-YYYY');
v_end_date := to_date('08-MAY-2004','DD-MON-YYYY');

Hi,

I understand your requirement to re-run a script as if on a given day, but would say that it is very rare (okay - once) that I have seen a database which truly supports this.

9th May 04: A script should run
10th May 04: someone moves house and has a new address
11th May 04: a script is rerun for the 9th ... differing results to the actual 9th.

While an address change sounds fairly innocuous, what if their tax status had changed in a financial reporting system? What if (as is often the case) certain unknowns are derived from a best guess at the time?

In any case, should you use data in force at the time - or data now known to be correct.

In the case of re-sending tax documents, use their tax status in force at the time, while sending it to the current address.

"Historisation and versioning" is a term I have heard applied to this.

Hth
Bill

Bill I am querying off of history tables not current tables.

This query is still not working.
The procedure executes fine but no results are returned and I know data is there because I see it in the tables.

What am I doing wrong?

declare
v_start_date date;
v_end_date date;
v_ralpha number;
v_ralpha_no_result number;
v_rcr_searching number;
v_rcr_not_searching number;
v_rcategory number;
v_rcategory_no_result number;
v_rbad_category number;
v_total_cr number;

begin
select to_date ('9-May-04') into v_start_date from dual;
select (to_date('8-May-04'))+1 into v_end_date from dual;



delete pdt_info;

SELECT COUNT(se_type_code) into v_ralpha
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty <> 0
AND se_type_code = 1
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

SELECT COUNT(se_type_code) into v_ralpha_no_result
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty = 0
AND se_type_code = 1
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

insert into pdt_info
select distinct caller_rec_no
from tinfo_req_hist
where info_req_start_time between v_start_date and v_end_date
and caller_rec_no not in (9999, 8888);


SELECT COUNT(*) into v_rcr_searching
FROM pdt_info;

SELECT COUNT(caller_rec_no) into v_total_cr
FROM tcaller_rec_hist
WHERE start_time between v_start_date and v_end_date;

v_rcr_not_searching:= v_total_cr - v_rcr_searching;

SELECT COUNT(se_type_code) into v_rcategory
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty <> 0
AND se_type_code in (105, 110)
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

SELECT COUNT(se_type_code) into v_rcategory_no_result
FROM tquery_text_hist q, tinfo_req_hist i
WHERE q.caller_rec_no = i.caller_rec_no
AND q.info_req_no = i.info_req_no
AND tot_qry_qty = 0
AND se_type_code in (105, 110)
and i.caller_rec_no not in (9999, 8888)
AND info_req_start_time between v_start_date and v_end_date;

SELECT COUNT(caller_rec_no) into v_rbad_category
FROM tval_error_hist
where se_type_code = 105
AND error_time between v_start_date and v_end_date;

insert into toperations (event_date, alpha, alpha_no_result, cr_searching, cr_not_searching, category, category_no_result, bad_category)
select v_start_date, v_ralpha, v_ralpha_no_result, v_rcr_searching, v_rcr_not_searching, v_rcategory, v_rcategory_no_result, v_rbad_category
from dual;


end;
/

Hi,

At the beginning you have this...
Essentially this means start and end date are incorrectly both set to 9th May '04.

If you want to run this script as it would have ran on the 9th May, this should be...

Also your original code (using sysdate) would also return a time portion. Depending on the rest of your code (specifically the between condition and content of your date column) this may or may not be important.

Hth
Bill

Bill,

I feel like a big idiot! What you said makes sense!

Thank you!
I am going to try to run it now!