CASE WHEN NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0) < 0
THEN 0
WHEN NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0) > 3
THEN 3
ELSE NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)
END AS NUM1
----------等价的算法
DECODE ( SIGN(NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)) + SIGN(NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0) - 3),
2,3,
-2,0,
NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)
)
THEN 0
WHEN NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0) > 3
THEN 3
ELSE NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)
END AS NUM1
----------等价的算法
DECODE ( SIGN(NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)) + SIGN(NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0) - 3),
2,3,
-2,0,
NVL(TO_NUMBER(COL2 - SUBSTR(COL1, 0,6)), 0)
)