Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2013
    Posts
    23

    Unanswered: ORA-00904: "DATEADD": invalid identifier

    Im getting an error message... wondering if it could be something to do with the date format, would appreciate some help...thanks

    SELECT RE_EZPAY_AT_AGENCY_DAILY.SNAPSHOT_DATE,
    RE_EZPAY_AT_AGENCY_DAILY.AGG_ORDER_NO,
    RE_EZPAY_AT_AGENCY_DAILY.DEBT,
    RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO_WCD,
    RE_EZPAY_AT_AGENCY_DAILY.STATUS,
    VE_MEMBER.MEMBER_JOIN_DATE
    FROM RE_EZPAY_AT_AGENCY_DAILY
    INNER JOIN VE_MEMBER
    ON RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO = VE_MEMBER.MEMBER_NO
    WHERE VE_MEMBER.MEMBER_JOIN_DATE <DATEADD(YEAR, -1, GETDATE())

    ORA-00904: "DATEADD": invalid identifier
    00904. 00000 - "%s: invalid identifier"
    *Cause:
    *Action:
    Error at Line: 11 Column: 35

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Oracle does not have any DATEADD function
    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.

  3. #3
    Join Date
    Sep 2013
    Posts
    23

    >365 days

    do you know what I would use instead to create something similar? I would need to find members who joined more than 365 days ago

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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
    Sep 2013
    Posts
    23
    Thankyou! ...managed to do it using WHERE VE_MEMBER.MEMBER_JOIN_DATE < sysdate -365

  6. #6
    Join Date
    Sep 2013
    Posts
    23
    I'm trying to now use this as an update but i'm obviously going wrong somewhere!

    UPDATE RE_EZPAY_AT_AGENCY_DAILY
    SET STATUS = 'R'
    WHERE VE_MEMBER.MEMBER_JOIN_DATE < sysdate -365
    AND STATUS = 'O'
    INNER JOIN VE_MEMBER
    ON RE_EZPAY_AT_AGENCY_DAILY.MEMBER_NO = VE_MEMBER.MEMBER_NO;

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See my answer in your new thread.

    BTW, thanks for opening the new thread. That makes life so much easier for other people that come later looking for the same kind of question!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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