Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: DELETE Dates from a list Problem

    Hi Guys,

    I have this strange problem. Take the following SQL i wrote which
    erturns a list of dates lower than the one entered in a table :-

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
    )

    UNION

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,1,2) < '03'
    AND
    SUBSTR(ACTVTY_DT,5,2) = '02'
    )

    UNION

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE ACTVTY_DT IN

    (SELECT ACTVTY_DT FROM HACPRP.EQ_POOL_ACT_VIEW

    WHERE SUBSTR(ACTVTY_DT,3,2) < '27'
    AND
    SUBSTR(ACTVTY_DT,1,2) = '03'
    AND
    SUBSTR(ACTVTY_DT,5,2) = '02'
    );

    I had to do this in this format as whoever created the table made the ACTVTY_DT a CHAR(06) instead of a DATE !
    This works fine and gives me back my required results.

    The problem is however - How do I get SQL to delete these results now
    from the HACPRP.EQ_POOL_ACT_VIEW table ?

    No matter what i try it just falls over and will not delete the records.
    Is it possible to do ?

    I really need help on this one, so any comments would be really appreciated.

    Peter.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    first of all, run the following and see if it doesn't produce exactly the same results as your UNION --

    SELECT * FROM HACPRP.EQ_POOL_ACT_VIEW
    WHERE SUBSTR(ACTVTY_DT,5,2) < '02'
    OR ( SUBSTR(ACTVTY_DT,1,2) < '03'
    AND SUBSTR(ACTVTY_DT,5,2) = '02'
    )
    OR ( SUBSTR(ACTVTY_DT,3,2) < '27'
    AND
    SUBSTR(ACTVTY_DT,1,2) = '03'
    AND
    SUBSTR(ACTVTY_DT,5,2) = '02'
    )


    if it doesn't, i'd like to know why

    if it does, just change the SELECT * at the beginning to DELETE

    rudy
    http://r937.com/

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Thanks Rudy

    Hi Rudy,

    Thanks for your suggestion, and yes it worked fine !!
    Sql is so strange.

    Thank you so much.

    Peter.

Posting Permissions

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