plsql联合中同一列的不同数据类型

Question

我有一个查询，它获取单个员工的详细信息，然后根据位置汇总总计，最后是总计。在第一个 select 语句中，我需要获取 max(column9)，但在联合中我需要获取 sum(column9)。我收到错误“数据类型不匹配”。以下是查询：

SELECT COLUMN1 AS LASTNAME,
         COLUMN2 AS FIRSTNAME,
         COLUMN3 AS LOCATION,
         SUM(COLUMN4) AS ACTIVITYNM1,
         SUM(COLUMN5) AS ACTIVITYNM2,
         SUM(COLUMN6) AS ACTIVITYNM3,
         SUM(COLUMN7) AS ACTIVITYNM4,
         SUM(COLUMN8) AS ACTIVITYNM5,
         MAX(COLUMN9) AS REG_HRS,
         MAX(COLUMN10) AS OT_HRS,
         MAX(COLUMN11) AS TOTAL_HRS,
         SUM(COLUMN12) AS PRODUCTIVITY_PERCENTAGE,
         COLUMN13 AS FULL_LOCATION                     
    FROM TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE))
GROUP BY COLUMN1, COLUMN2, COLUMN3, COLUMN13
  UNION     
  SELECT NULL,
         NULL,
         CONCAT(SUBSTR(COLUMN3,3,3),' Total') AS LOCATION,
         SUM(COLUMN4) AS ACTIVITYNM1,
         SUM(COLUMN5) AS ACTIVITYNM2,
         SUM(COLUMN6) AS ACTIVITYNM3,
         SUM(COLUMN7) AS ACTI,
         VITYNM4,
         SUM(COLUMN8) AS ACTIVITYNM5,
         SUM(COLUMN9) AS REG_HRS,
         SUM(COLUMN10) AS OT_HRS,
         SUM(COLUMN11) AS TOTAL_HRS,
         (SUM(COLUMN12)/COUNT(DISTINCT(COLUMN1))) AS PRODUCTIVITY_PERCENTAGE,
         COLUMN13 AS FULL_LOCATION
    FROM TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE))
GROUP BY COLUMN3, COLUMN13

=============================================== ==============================================

您好，非常感谢您的所有答案。我得到了解决方案。我在第二个联合内使用子查询。我将代码粘贴在这里。

SELECT 
   COLUMN1 AS EMPID,
   COLUMN2 AS FIRSTNAME,
   COLUMN3 AS LASTNAME,
       COLUMN4 AS LOCATION,
       SUM(COLUMN5) AS ACTIVITYNM1,
       SUM(COLUMN6) AS ACTIVITYNM2,
       SUM(COLUMN7) AS ACTIVITYNM3,
       SUM(COLUMN8) AS ACTIVITYNM4,
       SUM(COLUMN9) AS ACTIVITYNM5,
        MIN(COLUMN10) AS EVENTDATE,
       TO_NUMBER(COLUMN11) AS REG_HRS,
       TO_NUMBER(COLUMN12) AS OT_HRS,
       TO_NUMBER(COLUMN13) AS TOTAL_HRS,
       SUM(COLUMN14) AS PRODUCTIVITY_PERCENTAGE

FROM 
   TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE))
GROUP BY 
   COLUMN1,
   COLUMN2,
   COLUMN3,
   COLUMN4,
   COLUMN11,
   COLUMN12,
   COLUMN13
UNION 

SELECT
   NULL AS EMPID,
   NULL AS LASTNAME,
   NULL AS FIRSTNAME,
       SUBSTR(INNER_REC.LOCATION,2,5) AS LOCATION,
       SUM(INNER_REC.ACTIVITYNM1) AS ACTIVITYNM1,
       SUM(INNER_REC.ACTIVITYNM2) AS ACTIVITYNM2,
       SUM(INNER_REC.ACTIVITYNM3) AS ACTIVITYNM3,
       SUM(INNER_REC.ACTIVITYNM4) AS ACTIVITYNM4,
       SUM(INNER_REC.ACTIVITYNM5) AS ACTIVITYNM5,
       MIN(INNER_REC.EVENTDATE) AS EVENTDATE,
       SUM(INNER_REC.REG_HRS) AS REG_HRS,
       SUM(INNER_REC.OT_HRS) AS OT_HRS,
       SUM(INNER_REC.TOTAL_HRS) AS TOTAL_HRS,
       SUM(INNER_REC.PRODUCTIVITY_PERCENTAGE) AS PRODUCTIVITY_PERCENTAGE
  FROM
  (
SELECT 
   COLUMN1 AS EMPID,
   NULL AS LASTNAME,
   NULL AS FIRSTNAME,
       COLUMN4 AS LOCATION,
       SUM(COLUMN5) AS ACTIVITYNM1,
       SUM(COLUMN6) AS ACTIVITYNM2,
       SUM(COLUMN7) AS ACTIVITYNM3,
       SUM(COLUMN8) AS ACTIVITYNM4,
       SUM(COLUMN9) AS ACTIVITYNM5,
       MIN(COLUMN10) AS EVENTDATE,
      SUM(TO_NUMBER(COLUMN11))/(SELECT COUNT(1) FROM TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE)) AB WHERE AB.COLUMN4 = 

CD.COLUMN4 AND AB.COLUMN1 = CD.COLUMN1) AS REG_HRS,
       SUM(TO_NUMBER(COLUMN12))/(SELECT COUNT(1) FROM TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE)) AB WHERE AB.COLUMN4 = 

CD.COLUMN4 AND AB.COLUMN1 = CD.COLUMN1) AS OT_HRS,
       SUM(TO_NUMBER(COLUMN13))/(SELECT COUNT(1) FROM TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE)) AB WHERE AB.COLUMN4 = 

CD.COLUMN4 AND AB.COLUMN1 = CD.COLUMN1) AS TOTAL_HRS,
       SUM(COLUMN14) AS PRODUCTIVITY_PERCENTAGE      
FROM 
   TABLE(ACTY_COL(V_STARTDATE,V_ENDDATE)) CD
GROUP BY 
   COLUMN4,
   COLUMN1
 ) INNER_REC
 GROUP BY
    INNER_REC.LOCATION

Answer 1

在第二个查询中尝试 to_char(null) 而不是仅 null 。另外，如果两个结果集是互斥的或者您不关心重复项，那么我建议您使用 UNION ALL 而不是仅使用 UNION（如果不需要，无需告诉 Oracle 执行删除重复项的额外步骤）。

Answer 2

我猜 COLUMN9 是字符数据类型。 SUM 可能会隐式转换为数值，但 MAX 将其视为 VARCHAR2（即“9”将大于“10”）。

所以我怀疑你想在第一个中使用 MAX(TO_NUMBER(COLUMN9)) ，或者更改 ACTY_COL 的定义，以便 COLUMN9 作为数字返回。

Answer 3

你能将每个都转换为相同的类型吗？

编辑：如果您使用的是 9i 或更高版本：

http: //download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016.htm

我已经有一段时间没有使用Oracle了，但是我最近在SQL Server中遇到了这个问题，其中一个null第一组 UNION 查询导致第二组中相应列的格式古怪。 SQL Server 中的答案是 CAST（colFromFirstSet as myDesiredDataType），以便引擎知道如何处理第二组中的相应列。我假设 Oracle 中类似的显式转换可能会解决不匹配的数据类型错误。