We have an Oracle view “view1”, which SQL is like this:

select ...,
       nvl(iac.indirect_attri_cost, 0) + nvl(pc.premises_cost, 0) + nvl(cc.central_cost, 0) total_cost
from   (select ..., cc.CENTRAL_COST 
        from   .. cc, .. rl
        where  ...) cc
full   outer join (select ...) iac
on     ...
full   outer join (select ...) pc
on     ...
left   join ... dept
on     ...

Note the CENTRAL_COST field we shown.

It works fine.

But our another view, “view2”, uses this view1, and it crashes with “ORA-01790 expression must have same datatype as corresponding expression”:

select * from view1

Yes, just like that.

We realized that something wrong in datatypes, ok, then we found a workaround done in View1, which is to wrap the column with TO_CHAR, and TO_NUMBER it later (for calculation purpose):

select ...,
       nvl(iac.indirect_attri_cost, 0) + nvl(pc.premises_cost, 0) + nvl(TO_NUMBER(cc.central_cost), 0) total_cost
from   (select ..., TO_CHAR(cc.CENTRAL_COST)
        from   .. cc, .. rl
        where  ...) cc
full   outer join (select ...) iac
on     ...
full   outer join (select ...) pc
on     ...
left   join ... dept
on     ...

This modified view1 works fine in view2.But we do not understand what’s wrong and how it is solved!

Even further, we found that applying different Optimization goal in view1 would bypass that error. This even confused us more!

Could anyone gives some insight? Thanks much.