I want to do this equation even if some values are not entered, a result appears-CodePudding

TO_CHAR((100-NVL(CO2 CO MOIST NH3_PPM NH3 CH4 N2 AR,0)),'99.09') as "H2",

CodePudding user response：

To me, it looks as if you wanted multiple NVLs:

TO_CHAR((100 - (
                NVL(CO2    , 0)  
                NVL(CO     , 0)  
                NVL(MOIST  , 0)  
                NVL(NH3_PPM, 0)  
                NVL(NH3    , 0)   
                NVL(CH4    , 0)  
                NVL(N2     , 0)  
                NVL(AR     , 0)
               ), '99.09') AS h2

CodePudding user response：

select TO_CHAR((100-NVL(CO2 CO MOIST NH3_PPM NH3 CH4 N2 AR,0)),'99.09') as "H2" from table;

NVL in this query is pointless since summation of different columns may not be null and NVL only goes for the default value if the first value is null.

Even if I do not understand what is your exact request/error, you may use case when statements or seperate nvl statements for each column.