gram77
asked on
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_ac count 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
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_ac
8 adhoc.temp_presc_num_TEST t,
9 retail.prescrip_retail partition (PRESC200705) p,
10 sherlock.sherlock_product_
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.