Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2004
    Posts
    4

    Unanswered: porting a query from informaix to oracle

    I'd like to port the below given query from informix to oracle

    DELETE FROM table_name
    WHERE ROWID >= :min_row_id
    AND ROWID < (:min_row_id + :rows_to_purge)
    AND date <= (:inf_date);

    it gets min_row_id from the below given query

    SELECT DECODE (MIN(ROWID),NULL,-1,MIN(ROWID))
    INTO :min_row_id
    FROM table_name
    WHERE sett_date <= (:inf_date);

    where rows_to_purge = 100 and inf_date is a date in informix format

    i understand oracle do not use numbers for rowid, is there anyway i can use the query similar to informix (i.e with numbers) in oracle ?
    any thoughts ?

    Cheers
    --Binoy

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    What is it that you want to accomplish? Apparently this SQL deletes some random 100 rows from "middle" of the table. Why would you want to do that? Is the intention in fact to delete all the rows from the table, in chunks of 100 at a time?

  3. #3
    Join Date
    Sep 2004
    Posts
    4
    Thanks for your reply!

    I dont wanna delete all the rows but delete 100 records older a particular date.

    Cheers
    --Binoy

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    OK, you can do this then:

    DELETE FROM table_name
    WHERE date <= :inf_date
    AND ROWNUM <= :rows_to_purge;

  5. #5
    Join Date
    Sep 2004
    Posts
    4
    It works
    Thanks Tony!

Posting Permissions

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