Missing expression in Oracle Function

Question

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.

Answer 1

This seems to be wrong:

AND EXTRACT (YEAR FROM cop.START_DAT) = EXTRACT (YEAR FROM DATE to_date ( adj_date , 'YYYY-MM-DD'))
                                                           ----

Remove DATE keyword (underlined in above piece of code), presuming that adj_date is a string in yyyy-mm-dd format which will then be converted to a valid date datatype value so that year can be extracted from it).