时间戳

首页 > 解决方案 > Oracle SQL 中的字符集不匹配

sql - Oracle SQL 中的字符集不匹配

问题描述

在 Oracle SQL Developer 3.1.07 中，下面的两个子查询可以自行正确运行。我要做的就是将它们合并在一起，但 Oracle 给出了 ORA-12704: 字符集不匹配错误。我检查了类似的帖子以检查 UNION 语法，但我不明白错误来自哪里。select 语句从两个查询中的相同列中选择字段，因此应该没有数据类型问题。

    SELECT assessmentid,
       claimdate,
       firstdiagnosis,
       affectedsystemdesc,
       thirdparty,
       SUM(settledamount) AS sam,
       param
  FROM (
  SELECT x.xclaimassessment.assessmentid,
         x.xclaimassessment.thirdparty,
         x.xclaimassessment.firstdiagnosis,
         x.xclaimassessment.affectedsystemdesc,
         x.xclaimassessment.claimdate,
         x.xclaimtreatmentinvoice.settledamount,
         'Q1' AS param
    FROM x.xclaimassessment left
    JOIN x.xclaimtreatment
  ON x.xclaimassessment.assessmentid = x.xclaimtreatment.assessmentid
    LEFT JOIN x.xclaimtreatmentinvoice
  ON x.xclaimtreatment.treatmentid = x.xclaimtreatmentinvoice.treatmentid
   WHERE ( ( icd9 = '501'
      OR icd9 = '162' )
     AND thirdparty = 'N' )
     AND months_between(
    SYSDATE,
    TO_DATE(
      x.xclaimassessment.claimdate
    )
  ) < 25
)
 GROUP BY assessmentid,
          claimdate,
          firstdiagnosis,
          affectedsystemdesc,
          thirdparty,
          param
UNION
SELECT assessmentid,
       claimdate,
       firstdiagnosis,
       affectedsystemdesc,
       thirdparty,
       SUM(settledamount) AS sam,
       param
  FROM (
  SELECT x.xclaimassessment.assessmentid,
         x.xclaimassessment.thirdparty,
         x.xclaimassessment.firstdiagnosis,
         x.xclaimassessment.affectedsystemdesc,
         x.xclaimassessment.claimdate,
         x.xclaimtreatmentinvoice.settledamount,
         'Q2' AS param
    FROM x.xclaimassessment left
    JOIN x.xclaimtreatment
  ON x.xclaimassessment.assessmentid = x.xclaimtreatment.assessmentid
    LEFT JOIN x.xclaimtreatmentinvoice
  ON x.xclaimtreatment.treatmentid = x.xclaimtreatmentinvoice.treatmentid
   WHERE months_between(
    SYSDATE,
    TO_DATE(
      x.xclaimassessment.claimdate
    )
  ) < 25
)
 GROUP BY assessmentid,
          claimdate,
          firstdiagnosis,
          affectedsystemdesc,
          thirdparty,
          param
HAVING SUM(settledamount) > 50000;

帮助将不胜感激！

标签： sqloracleoracle-sqldeveloper

解决方案

好的，感谢我的经理 K 先生，他真正解决了这个问题。

问题出在 THIRDPARTY 列，它在两个子查询中是相同的数据类型。为了解决这个问题，我们在两个子查询中都使用了 CAST（字段AS VARCHAR(3)）。

我们通过从查询中删除除第一个字段之外的所有字段，然后将它们一个接一个地添加回来，直到它损坏，从而确定了问题字段。

我们仍然不明白为什么会发生此错误，因此有关此的任何信息仍然会被应用。这是解决的代码：

SELECT ASSESSMENTID,CLAIMDATE, FIRSTDIAGNOSIS, AFFECTEDSYSTEMDESC, THIRDP, SUM(SETTLEDAMOUNT) AS SAM, PARAM
FROM (
      SELECT X.XCLAIMASSESSMENT.ASSESSMENTID, CAST(X.XCLAIMASSESSMENT.THIRDPARTY AS VARCHAR(3)) AS THIRDP,
      X.XCLAIMASSESSMENT.FIRSTDIAGNOSIS, X.XCLAIMASSESSMENT.AFFECTEDSYSTEMDESC, 
      X.XCLAIMASSESSMENT.CLAIMDATE, X.XCLAIMTREATMENTINVOICE.SETTLEDAMOUNT, 'Q1' as PARAM
      FROM X.XCLAIMASSESSMENT
      LEFT JOIN X.XCLAIMTREATMENT
      ON X.XCLAIMASSESSMENT.ASSESSMENTID = X.XCLAIMTREATMENT.ASSESSMENTID
      LEFT JOIN X.XCLAIMTREATMENTINVOICE
      ON X.XCLAIMTREATMENT.TREATMENTID = X.XCLAIMTREATMENTINVOICE.TREATMENTID
      WHERE ((ICD9 = '501' or ICD9 = '162') and THIRDPARTY = 'N')and MONTHS_BETWEEN(SYSDATE,to_date(X.XCLAIMASSESSMENT.CLAIMDATE))< 25)
      GROUP BY  ASSESSMENTID, CLAIMDATE, FIRSTDIAGNOSIS, AFFECTEDSYSTEMDESC, THIRDP, PARAM
      UNION
SELECT ASSESSMENTID, CLAIMDATE, FIRSTDIAGNOSIS, AFFECTEDSYSTEMDESC, THIRDP, SUM(SETTLEDAMOUNT) AS SAM, PARAM
FROM(
      SELECT X.XCLAIMASSESSMENT.ASSESSMENTID, CAST(X.XCLAIMASSESSMENT.THIRDPARTY AS VARCHAR(3)) AS THIRDP,
      X.XCLAIMASSESSMENT.FIRSTDIAGNOSIS, X.XCLAIMASSESSMENT.AFFECTEDSYSTEMDESC, 
      X.XCLAIMASSESSMENT.CLAIMDATE, X.XCLAIMTREATMENTINVOICE.SETTLEDAMOUNT, 'Q2' as PARAM
      FROM X.XCLAIMASSESSMENT
      LEFT JOIN X.XCLAIMTREATMENT
      ON X.XCLAIMASSESSMENT.ASSESSMENTID = X.XCLAIMTREATMENT.ASSESSMENTID
      LEFT JOIN X.XCLAIMTREATMENTINVOICE
      ON X.XCLAIMTREATMENT.TREATMENTID = X.XCLAIMTREATMENTINVOICE.TREATMENTID
      WHERE MONTHS_BETWEEN(SYSDATE,to_date(X.XCLAIMASSESSMENT.CLAIMDATE))< 25)
      GROUP BY ASSESSMENTID, CLAIMDATE, FIRSTDIAGNOSIS, AFFECTEDSYSTEMDESC, THIRDP, PARAM
      HAVING SUM(SETTLEDAMOUNT) > 50000;

推荐阅读