different datatypes for same column in union in plsql

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:

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_S开发者_如何学PythonTARTDATE,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

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).

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').

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.

Can you cast each to the same type?

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

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.