I have problem with PL SQL Function
function isConditionsForChargeChanged(adj_date varchar2, acc_num varchar2) return integer
is
res integer;
begin
SELECT CASE when EXISTS (
select DISTINCT 1
from custhasproduct@rbm_dblink chp
join custproductdetails@rbm_dblink cpd on chp.customer_ref = cpd.customer_ref and chp.product_seq = cpd.product_seq
join custproducttariffdetails@rbm_dblink cptd on cptd.customer_ref = cpd.customer_ref and cptd.product_seq = cpd.product_seq
join custproductstatus@rbm_dblink cps on cps.customer_ref = chp.customer_ref and cps.product_seq = chp.product_seq
join tariff@rbm_dblink tr on tr.tariff_id = cptd.tariff_id and tr.catalogue_change_id in (select catalogue_change_id from cataloguechange where catalogue_status=3)
join custoverrideprice@rbm_dblink cop on cop.customer_ref = cpd.customer_ref and cop.product_seq = cpd.product_seq
where cpd.account_num = acc_num
and ((extract (month from cps.effective_dtm) = extract (month from to_date(adj_date, 'YYYY-MM-DD'))
and extract (year from cps.effective_dtm) = extract (year from to_date(adj_date, 'YYYY-MM-DD'))
AND cps.product_status in ('SU', 'OK', 'TX')) /* Проверка изменения активности услуги в данном отчетном периоде */
OR (extract (month from cptd.START_DAT) = extract (month from to_date(adj_date, 'YYYY-MM-DD'))
and extract (year from cptd.START_DAT) = extract (year from to_date(adj_date, 'YYYY-MM-DD')) AND cptd.END_DAT IS NULL) /* Проверка на смену тарифа в данном отчетном периоде*/
OR (extract (month from cop.START_DAT) = extract (month from to_date(adj_date, 'YYYY-MM-DD'))
and extract (year from cop.START_DAT) = extract (year from DATE to_date(adj_date, 'YYYY-MM-DD')) AND COP.END_DAT IS NULL)) /* Проверка на переопределение цены в данном отчетном периоде */
)
THEN 0
else 1 end into res
from dual;
return res;
END isConditionsForChargeChanged;
Error message:
PL/SQL: ORA-00936: Missing expression Line 190 Pos 46
PL/SQL: SQL Statement ignored Line 181 Pos 3
Line 190 Pos 46 (mark with (?)):
...and ((extract (month from cps.effective_dtm) (?)= extract (month from to_date(adj_date, 'YYYY-MM-DD'))...
Line 181 Pos 3 (mark with (?)):
...(?)SELECT CASE when EXISTS (...
What could be the problem? I don’t see any gross syntax violations.
New contributor
Nadine241197 is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.