-
-
haocx 数据老手Lv5
发表于2018-9-25 15:35
悬赏10
已解决
楼主
本帖最后由 haocx 于 2018-9-25 15:58 编辑
一层的公式：if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0101' | V_LES_CONTRACT_KPZY.BUSINESS_NATURE='04',V_LES_CONTRACT_KPZY.OUTSTAND_BALANCE_RMB,0)
两层的公式：if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0101' | V_LES_CONTRACT_KPZY.BUSINESS_NATURE='04',V_LES_CONTRACT_KPZY.OUTSTAND_BALANCE_RMB,if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0104',V_LES_CONTRACT_KPZY.CAP_NET_AMT_RMB,null)),一层的时候，看sql出现的是sum自动加和，两层出现的是max取了最大值，全连接设了没用，指标勾了和去掉了也没用，进行了对两层的，值的加sum求和，会报空指针异常
SQL：
一层：select row_.*,rownum from ( select count(distinct a.PAPER_CONTRACT_NO) as C2,sum(case when (a.BUSINESS_NATURE = '0101') OR (a.BUSINESS_NATURE = '04') then a.OUTSTAND_BALANCE_RMB else 0 end) as D2,a.BUSINESS_NATURE as B2,a.USERID_ as A2,max(b.NAME) as mc_B20,max(c.NAME) as mc_A21
from V_LES_CONTRACT_KPZY a
,DIM_ZCYWXZ b
,DIM_JG c
where (A.BUSINESS_NATURE=B.ID(+)) and (A.USERID_=C.ID(+)) and (( SUBSTR(a.BBQ,1,6) = '201806') AND (a.USERID_ like '020%'))
group by a.USERID_,a.BUSINESS_NATURE )row_ where rownum <= 1000 两层：select row_.*,rownum from ( select count(distinct a.PAPER_CONTRACT_NO) as C2,max(case when (a.BUSINESS_NATURE = '0101') OR (a.BUSINESS_NATURE = '04') then a.OUTSTAND_BALANCE_RMB else case when a.BUSINESS_NATURE = '0104' then a.CAP_NET_AMT_RMB else NULL end end) as D2,a.BUSINESS_NATURE as B2,a.USERID_ as A2,max(b.NAME) as mc_B20,max(c.NAME) as mc_A21
from V_LES_CONTRACT_KPZY a
,DIM_ZCYWXZ b
,DIM_JG c
where (A.BUSINESS_NATURE=B.ID(+)) and (A.USERID_=C.ID(+)) and (( SUBSTR(a.BBQ,1,6) = '201806') AND (a.USERID_ like '020%'))
group by a.USERID_,a.BUSINESS_NATURE )row_ where rownum <= 1000
一层的公式：if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0101' | V_LES_CONTRACT_KPZY.BUSINESS_NATURE='04',V_LES_CONTRACT_KPZY.OUTSTAND_BALANCE_RMB,0)
两层的公式：if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0101' | V_LES_CONTRACT_KPZY.BUSINESS_NATURE='04',V_LES_CONTRACT_KPZY.OUTSTAND_BALANCE_RMB,if(V_LES_CONTRACT_KPZY.BUSINESS_NATURE='0104',V_LES_CONTRACT_KPZY.CAP_NET_AMT_RMB,null)),一层的时候，看sql出现的是sum自动加和，两层出现的是max取了最大值，全连接设了没用，指标勾了和去掉了也没用，进行了对两层的，值的加sum求和，会报空指针异常
SQL：
一层：select row_.*,rownum from ( select count(distinct a.PAPER_CONTRACT_NO) as C2,sum(case when (a.BUSINESS_NATURE = '0101') OR (a.BUSINESS_NATURE = '04') then a.OUTSTAND_BALANCE_RMB else 0 end) as D2,a.BUSINESS_NATURE as B2,a.USERID_ as A2,max(b.NAME) as mc_B20,max(c.NAME) as mc_A21
from V_LES_CONTRACT_KPZY a
,DIM_ZCYWXZ b
,DIM_JG c
where (A.BUSINESS_NATURE=B.ID(+)) and (A.USERID_=C.ID(+)) and (( SUBSTR(a.BBQ,1,6) = '201806') AND (a.USERID_ like '020%'))
group by a.USERID_,a.BUSINESS_NATURE )row_ where rownum <= 1000 两层：select row_.*,rownum from ( select count(distinct a.PAPER_CONTRACT_NO) as C2,max(case when (a.BUSINESS_NATURE = '0101') OR (a.BUSINESS_NATURE = '04') then a.OUTSTAND_BALANCE_RMB else case when a.BUSINESS_NATURE = '0104' then a.CAP_NET_AMT_RMB else NULL end end) as D2,a.BUSINESS_NATURE as B2,a.USERID_ as A2,max(b.NAME) as mc_B20,max(c.NAME) as mc_A21
from V_LES_CONTRACT_KPZY a
,DIM_ZCYWXZ b
,DIM_JG c
where (A.BUSINESS_NATURE=B.ID(+)) and (A.USERID_=C.ID(+)) and (( SUBSTR(a.BBQ,1,6) = '201806') AND (a.USERID_ like '020%'))
group by a.USERID_,a.BUSINESS_NATURE )row_ where rownum <= 1000
最近看过此主题的会员
8个回答
if嵌套的情况下也希望表达式能求和，试试在表达式加算子_s()
例如：V_LES_CONTRACT_KPZY.CAP_NET_AMT_RMB 变成 _s(V_LES_CONTRACT_KPZY.CAP_NET_AMT_RMB)
tangmq 发表于 2018-9-25 15:57
if嵌套的情况下也希望表达式能求和，试试在表达式加算子_s()
例如：V_LES_CONTRACT_KPZY.CAP_NET_AMT_RMB ...
试了，不行，加了报错，空指针异常
