Oracle 11g – Does VARCHAR2 Convert to Numbers When Aggregated?

aggregateoracleoracle-11gvarchar

I have a table in an Oracle 11g database that's similar to the following:

CREATE TABLE BUDGET_DATA (
  BUDGET_MONTH varchar2(30)
  , BUDGET_CATEGORY varchar2(30)
  , BUDGET_AMOUNT varchar2(30)
)

All the values in BUDGET_AMOUNT are numbers. When I do select SUM, the values come out as I would have expected if BUDGET_AMOUNT was actually stored as a numeric.

SELECT BUDGET_MONTH, SUM(BUDGET_AMOUNT)
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

I'm curious, does Oracle perform a native conversion to number if you put a VARCHAR2 in an aggregate function? I would have expected that I'd have to do the following to get the data to sum correctly:

SELECT BUDGET_MONTH, SUM(TO_NUMBER(BUDGET_AMOUNT))
FROM BUDGET_DATA
GROUP BY BUDGET_MONTH

Can someone point me to some documentation about this behavior? I haven't been able to find anything to describe it.

Best Answer

Implicit Data Conversion

Oracle Database automatically converts a value from one data type to another when such a conversion makes sense.

You can find more on the above URL.

After you read it, forget everything written there, and use explicit conversion. Relying on implicit conversion is bad practice.

Related Solutions

Oracle 11g – Aggregate Function and Query

Simple, but "dirty":

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 3 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
Y

Change input (e.g. change id to 4 in last line):

with t as (
  select 1 as id, 'Y' as received from dual union
  select 1 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'N' as received from dual union
  select 2 as id, 'Y' as received from dual union
  select 3 as id, 'N' as received from dual union
  select 4 as id, 'Y' as received from dual 
)
select min(max(received)) from t group by id;

M
-
N

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression. As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end

Best Answer

Related Solutions

Oracle 11g – Aggregate Function and Query

ORA-00937 – Calculating Sub-Value in Aggregate SELECT Statement

Related Question