add_months function causing, invalid relational operator error

Posted on 2007-10-17
Last Modified: 2013-12-19
I want this query to pull 24 month past data,

so I appended add_months(p.month,-24)

However, this query results in an error:invalid relational operator due to add_months condition

abc@def>select r.pfizer_account_id,
  2   p.presc_num,
  3   spm.product_id,
  4   p.month,
  5   t.best_call_state,
  6   sum(p.trx_count)
  7  from ops$pyrcm.rlup_assigned_account r,
  8   adhoc.temp_presc_num_TEST t,
  9   retail.prescrip_retail partition (PRESC200705) p,
 10   sherlock.sherlock_product_mapping spm
 11  where spm.product_id like '056%'
 12   and add_months(p.month,-24)
 13   and spm.mds6 = p.product_id
 14   and t.CLIENT_ID = p.presc_num
 15   and r.pfizer_account_id = p.payer_plan
 16   and t.best_call_state = r.ST
 17  group by r.pfizer_account_id,  
 18   p.presc_num,
 19   spm.product_id,
 20   p.month,
 21   t.best_call_state;
 and spm.mds6 = p.product_id
ERROR at line 13:
ORA-00920: invalid relational operator
Question by:gram77
    LVL 142

    Accepted Solution

    >and add_months(p.month,-24)

     add_months() returs a date, you need to compare to some date value...

     you actually want to change to this:

     and p.month >= add_months(sysdate,-24)

      or, if you want 24 full months:

     and p.month >= add_months(trunc(sysdate),-24)
    LVL 21

    Assisted Solution

    To be a little more accurate:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
    This video shows how to recover a database from a user managed backup
    This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now