[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1030
  • Last Modified:

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
0
gram77
Asked:
gram77
2 Solutions
 
Guy Hengel [angelIII / a3]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
 
oleggoldCommented:
To be a little more accurate:
add_months(trunc(sysdate,'MONTH'),-24)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now