Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    113

    Unanswered: Need help with Sysdate

    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;

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    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

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    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?

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    to_date('9-MAY-2004','DD-MON-YYYY')
    (to_date('9-MAY-2004','DD-MON-YYYY'))-1

    HTH
    Gregg

  5. #5
    Join Date
    Apr 2004
    Posts
    113
    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;

  6. #6
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Correct ...

  7. #7
    Join Date
    Apr 2004
    Posts
    113
    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;

  8. #8
    Join Date
    May 2004
    Location
    alabama
    Posts
    5
    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

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >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');
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  11. #11
    Join Date
    Apr 2004
    Posts
    113
    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;
    /

  12. #12
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi,

    At the beginning you have this...
    Code:
    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;
    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...

    Code:
    select to_date ('08/05/2004','DD/MM/YYYY') into v_start_date from dual;
    select to_date('09/05/2004','DD/MM/YYYY') into v_end_date from dual;
    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
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  13. #13
    Join Date
    Apr 2004
    Posts
    113
    Bill,

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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •