CREATE OR REPLACE VIEW CESHI
(S_H_SUM_CN)
AS
select
to_char(decode(v.S_H_SUM_CN,0,null,v.S_H_SUM_CN),'99999999.99') as S_H_SUM_CN
from
(SELECT
NVL (s_h_cn, 0)+ NVL (s_h_hk, 0) * NVL (s_h_hk_exchange_rate, 0) AS s_h_sum_cn
FROM sales) as v
(S_H_SUM_CN)
AS
select
to_char(decode(v.S_H_SUM_CN,0,null,v.S_H_SUM_CN),'99999999.99') as S_H_SUM_CN
from
(SELECT
NVL (s_h_cn, 0)+ NVL (s_h_hk, 0) * NVL (s_h_hk_exchange_rate, 0) AS s_h_sum_cn
FROM sales) as v
s_h_hk
s_h_hk_exchange_rate
的取值可能?
语句可进一步优化,不用子查询
s_h_hk(港币)
s_h_hk_exchange_rate(港币汇率)只能取值为NUMBER.
如果为0,价格只能显示为NULL,不能显示0
(S_H_SUM_CN)
AS
SELECT to_char(s_h_cn+s_h_hk*s_h_hk_exchange_rate,'9999999.99') AS s_h_sum_cn
FROM sales
/
(S_H_SUM_CN)
AS
select to_char(nullif(NVL (s_h_cn, 0)+ NVL (s_h_hk, 0) * NVL (s_h_hk_exchange_rate, 0),0) ,'99999999.99') as S_H_SUM_CN
from sales
可以写成
NVL (s_h_hk*s_h_hk_exchange_rate, 0)