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

    Unanswered: Oracle Error: ORA-00933

    Hello,

    I am trying to delete duplicates from 2 tables:

    Delete tq_hist
    from ti_hist i, tq_hist q
    where i.caller_rec_no=q.caller_rec_no
    and q.info_req_no=q.info_req_no
    and i.caller_rec_no=9999
    and info_req_start_time='5/16/2004';

    and I keep getting the following error:

    ORA-00933 Sql Command not properly ended

    Please help!

  2. #2
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Hi!

    Try:

    Delete
    from ti_hist i, tq_hist q
    where i.caller_rec_no=q.caller_rec_no
    and q.info_req_no=q.info_req_no
    and i.caller_rec_no=9999
    and info_req_start_time='5/16/2004';


    Good luck!
    Regards,
    Julia

  3. #3
    Join Date
    Apr 2004
    Posts
    113
    Thank for the quick reply!

    But, I am still getting the same error.

  4. #4
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    Would you please give me the code of your ti_hist and tq_hist tables?
    Regards,
    Julia

  5. #5
    Join Date
    Apr 2004
    Location
    USA
    Posts
    33
    You cannot do that in Oracle (AFAIK). In SQLServer you can do it. For Oracle, you can try the following:

    Delete
    from ti_hist i
    where exists(select 1 from tq_hist q
    where i.caller_rec_no=q.caller_rec_no
    and i.caller_rec_no=9999);

    // and info_req_start_time='5/16/2004';

    (Not sure in info_req_start_time is in ti_hist or tq_hist)

    And for deleting from tq_hist,

    Delete from tq_hist q
    where not exists( select 1 from ti_hist i
    where i.caller_rec_no = q.caller_rec_no
    and i.caller_rec_no = 9999);

  6. #6
    Join Date
    Apr 2004
    Posts
    113
    Create Table Ti_hist
    (
    Caller_rec_no Number(10) Null,
    Info_req_no Number(30) Null,
    Market_code Char(2) Null,
    Info_req_reason_desc Char(30) Null,
    Info_req_start_time Date Null,
    Info_req_stop_time Date Null,
    Tot_qry_qty Number(10) Null,
    Serv_pntr_no Number(10) Null,
    Lang_id_code Number(10) Null
    )

    Create Table Tq_hist
    (
    Caller_rec_no Number(10) Null,
    Info_req_no Number(10) Null,
    Market_code Char(2) Null,
    Event_code Number(10) Null,
    Url_no Number(10) Null,
    Event_string_text Varchar2(255) Null,
    Event_time Date Null
    )

  7. #7
    Join Date
    Feb 2004
    Location
    Germany
    Posts
    136
    The code of Arvindram works on my system (Oracle 8.1.).
    Regards,
    Julia

  8. #8
    Join Date
    Apr 2004
    Posts
    113
    Thank you so much!
    They are running beautifilly now!

  9. #9
    Join Date
    Apr 2004
    Posts
    113
    arvindram,

    This query ran all day yesterday and never returned any results.

    Is there a way I can join the 2 queries together like I did on Sybase:

    Delete tq_hist
    from ti_hist i, tq_hist q
    where i.caller_rec_no=q.caller_rec_no
    and q.info_req_no=q.info_req_no
    and i.caller_rec_no=9999
    and info_req_start_time='5/16/2004';

  10. #10
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there a way I can join the 2 queries together like I did on Sybase:
    NO
    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.

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    58
    Quote Originally Posted by arvindram
    You cannot do that in Oracle (AFAIK). In SQLServer you can do it. For Oracle, you can try the following:

    Delete
    from ti_hist i
    where exists(select 1 from tq_hist q
    where i.caller_rec_no=q.caller_rec_no
    and i.caller_rec_no=9999);

    // and info_req_start_time='5/16/2004';

    (Not sure in info_req_start_time is in ti_hist or tq_hist)

    And for deleting from tq_hist,

    Delete from tq_hist q
    where not exists( select 1 from ti_hist i
    where i.caller_rec_no = q.caller_rec_no
    and i.caller_rec_no = 9999);

    Doesnt "where i.caller_rec_no=q.caller_rec_no
    and i.caller_rec_no=9999"
    just mean where i.caller_rec_no = q.caller_rec_no = 9999
    in this case cant we just say
    Delete
    from ti_hist i
    where i.caller_rec_no=9999
    I might be wrong this is just what I gather in the first go.

Posting Permissions

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