oracle - Oracle - Selecting not null result of calculation

Question

For every row of my data set, there exist data for the only one of the two options for calculation and the other columns are Null.

My goal is to find simplest way to select not null result of calculation for each row. Expected result:

ROW_NUM   result
-------- -------
1          4.5
2          4.56

My code:

With DATASET AS ( 
  -- column 1 is just for row number, 
  -- column 2 and 3 for caculation option1, 
  --- columns 4~6 for caculation option2
  SELECT 1 ROW_NUM, NULL time1, NULL qty1,  2   time2_1, 2.5  time2_2,  1 qty2 
  FROM DUAL
  UNION
  SELECT 2 ROW_NUM, 4.56 time1, 1   qty1,   NULL time2_1, NULL time2_2, NULL qty2 
  FROM DUAL
)
SELECT ROW_NUM, time1/qty1 OPTION1, (time2_1+time2_2)/qty2 OPTION2 
FROM DATASET;

Result:

 ROW_NUM   OPTION1 OPTION2
 -------- ------- ---------
    1                4.5
    2      4.56

标签： oracleselectnull

Answer 1

您可以在 null 时解码并使用不同的表示形式：

SELECT ROW_NUM, decode(time1/qty1,null,(time2_1+time2_2)/qty2,time1/qty1) result FROM DATASET;

或nvl

SELECT ROW_NUM, nvl(time1/qty1,(time2_1+time2_2)/qty2,time1/qty1) result FROM DATASET;

NVL 允许您在查询结果中将 null（返回为空白）替换为字符串。