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

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/4506071/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-18 22:16:04  来源:igfitidea点击:

different datatypes for same column in union in plsql

sqloracleunion

提问by Remya

I have a query which takes the individual employee details,then summary total according to the location and finally the grand total.In first select statement I need to take max(column9),but in the union I need to take sum(column9).I am getting the error "Data type mismatch". Following is the query:

我有一个查询,它获取单个员工的详细信息,然后根据位置进行汇总,最后是总计。在第一个选择语句中,我需要采用 max(column9),但在 union 中,我需要采用 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

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

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

Hi Thank you very much for all the answers. I got the solution.I use subquery inside the second union. I am pasting the code here.

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

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

回答by Eddie Awad

Try to_char(null) instead of just null in the second query. Also, if the two result sets are mutually exclusive or you do not care about duplicates then I suggest you use UNION ALL instead of just UNION (no need to tell Oracle to do this extra step of removing duplicates if not required).

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

回答by Gary Myers

I'm guessing COLUMN9 is a character datatype. The SUM is probably doing an implicit conversion to a numeric value but the MAX is treating it as a VARCHAR2 (ie '9' would be a larger than '10').

我猜 COLUMN9 是一种字符数据类型。SUM 可能正在对数值进行隐式转换,但 MAX 将其视为 VARCHAR2(即“9”将大于“10”)。

So I suspect you want to MAX(TO_NUMBER(COLUMN9)) in the first, or change the definition of ACTY_COL so that COLUMN9 is returned as numeric.

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

回答by Tim

Can you cast each to the same type?

你能把每个都转换成相同的类型吗?

EDIT: If you're using 9i or later:

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

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

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

It's been a while since I used Oracle, but I ran into this problem recently in SQL Server where a null in the first set of a UNION query was causing wacky formatting of the corresponding column in the second set. The answer in SQL Server was to CAST( colFromFirstSet as myDesiredDataType) so that the engine knew what to do with the corresponding column from the second set. I am supposing that a similar explicit cast in Oracle might address the mismatched datatype error.

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