数据库升级后有个视图查询报错： 

视图定义如下：

CREATE OR REPLACE VIEW *** AS

SELECT distinct fygsy,nvl(fykm2,fykmx)fykm2,zxmc, bxbm,hd.departmentname sqmc,ssdq,hd1.departmentname dqmc,f.requestid,F.lcbh,bxr tdr,hr.lastname,

    substr(fygsy,0,4) as dq_nd ,TO_CHAR(to_date(fygsy||'01','yyyy-mm-dd'), 'FMMM') AS dq_yf, TO_CHAR(to_date(fygsy||'01','yyyy-mm-dd'), 'Q') AS dq_jd,

    dt1.bxje fy,dt1.id mxid,to_char(dt1.zy) zy ,to_char(dt1.xmbh) xmbh

    FROM ** f ,formtable_main_1071_dt1 dt1,** hd ,** hd1,

    ** hr,** wcc

    WHERE f.id = dt1.mainid

    and wcc.requestid = F.requestid

    and wcc.currentnodetype <> 0

    and wcc.status not in ('提单人','报销人核对','省区经理')

    and (nvl(zxmc,99) in (4,7,8) or nvl(zxmc,99)=99 )

    and hd.id = bxbm

    and hd1.id = ssdq

    and bxr = hr.id

    and wcc.currentnodetype <> 0

    and dt1.fykm2 is not null

    and fygsy is not NULL

UNION

SELECT a.FYGSY,

       '业务招待费' as FYKM2,

       NULL AS zxmc,

     --  wr.STATUS,

       a.szbm as bxbm,

       hd.DEPARTMENTNAME sqmc,

       a.sspqxtb as ssdq,

       hd1.DEPARTMENTNAME dqmc,

       a.REQUESTID,

       a.SPBH,

       a.SQR tdr,

       hr.LASTNAME,

       substr(a.fygsy, 0, 4) as dq_nd,

       TO_CHAR(to_date(a.fygsy || '01', 'yyyy-mm-dd'), 'FMMM') AS dq_yf,

       TO_CHAR(to_date(a.fygsy || '01', 'yyyy-mm-dd'), 'Q') AS dq_jd,

      to_number( b.je) fy,

       b.id mxid,

       to_char(b.BZ) zy,

       to_char(null) xmbh

  FROM ** a

  LEFT JOIN ** b

    on a.ID = b.MAINID

  LEFT JOIN ** wr

    on wr.REQUESTID = a.requestid

  LEFT JOIN ** hd

    on hd.id = a.SZBM

  LEFT JOIN ** hd1

    on hd1.id = a.sspqxtb

  LEFT JOIN ** hr

    on hr.ID = a.SQR

  LEFT JOIN ** wcc

    ON WCC.REQUESTID = a.REQUESTID

 where wcc.currentnodetype <> 0

   and a. fygsy is not null

and wcc.status not in('提单人', '客户接待专员审核')

;

fygsy 样式为： 2025-10

执行下面的查询报错：

SELECT SUM(FY) FY

  FROM CRM_YXFYMX_V

 where fykm2 = '业务招待费'

   AND nvl(zxmc, 10) = 10

  AND DQ_ND = '2025'

  AND DQ_YF = '1'

   AND BXBM = '6509'

 GROUP BY BXBM, DQ_ND, DQ_YF

报错代码：

ORA-01840

应该也是之前的问题 升级后19c 的检查更加严格

我之前考虑是 TO_CHAR(TO_DATE(fygsy || '-01', 'yyyy-mm-dd'), 'Q') AS dq_jd 通过这种方式处理发现还是不行

通过下面的将-去掉后运行正常

TO_CHAR(TO_DATE(REPLACE(fygsy, '-', '') || '01', 'yyyymmdd'), 'Q') AS dq_jd

另外修改优化器兼容性参数不知道行不行 

optimizer_features_enable