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

    Unanswered: Deletes one from one table but not the other

    For some reason my 2 queries should work nicely but they don't.

    This query returns 0 results when I know it should be well over 280,000 rows:
    Last edited by newbie2004; 09-15-04 at 17:57.

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    did you rollback in between?

    and info_req_start_time doesn't have an alias - which table is it from, or both?
    Give a man a fish, you feed him for a day. Club him over the head with a fish, he'll leave you alone.

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    Shoblock,

    I think I figured this one out.
    Last edited by newbie2004; 09-15-04 at 17:58.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > and info_req_start_time='08-Sep-2004');
    bad, bad, bad coding technique by comparing a string to a date WITHOUT using TO_DATE
    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.

  5. #5
    Join Date
    Apr 2004
    Posts
    113
    Really? I did not know that. I am slowly learning pl/sql.

    DELETE tinfo_req_hist
    where caller_rec_no=8888
    and info_req_start_time=to_date('08-Sep-2004 00:00:00', 'DD-MON-YYYY HH24:MIS);

    Is this correct?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    > Is this correct?
    It is correct if & only if info_req_start_time ALWAYS has a time value of 00:00:00

    Alternatively use
    and TRUNC(info_req_start_time)=to_date('08-Sep-2004','DD-MON-YYYY);
    but this syntax would preclude the use on any index on INFO_REQ_START_TIME
    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.

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    What Anacedent is saying is that oracle is only going to match those
    dates that match the date & TIME exactly.

    If you want to delete ALL values during the whole DAY then you
    need to alter the date column either use TRUNC or convert it
    to to_char:
    PHP Code:
    delete tinfo_req_hist 
    where caller_rec_no 
    8888
    and to_char(info_req_start_time'DDMMYYYY') = '08092004'
    always consider the timestamp of the specified DATE.
    if you want all values for a full day, then convert the date column.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Apr 2004
    Posts
    113
    Cool beans!!!

    Thank you so much!

  9. #9
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    And to ellaborate a litle more: If you want to use the index on 'info_req_start_time' (assuming there's one, as anacedent warned you), you could use something like:

    Code:
    .. and info_req_start_time between to_date('08-sep-2004','dd-mon-yyyy)
                                        and to_date('08-sep-2004','dd-mon-yyyy)+1-1/24/60/60
    i.e.: subtracting one second from next day.

Posting Permissions

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