add_months function causing, invalid relational operator error

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
gram77Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
>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)
0
 
oleggoldConnect With a Mentor Commented:
To be a little more accurate:
add_months(trunc(sysdate,'MONTH'),-24)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.