首页 新闻 赞助 找找看

关于 case when语句

0
悬赏园豆:20 [待解决问题]

左连接的右边部分能查到数据,但是把字段写到select后面就没数据了。我猜想可能是第一行的case when 里的字段没加别名的原因,但是加别名又报错。新手  求大神解惑。

1 Select L20.SID,L10.FACTORY,L10.WO,L20.VORNR,L20.KTSCH,L20.PARTNO,L20.MAKTX,L20.WAREHOUSE,L20.LGPBE,L20.ERFMG,CASE WHEN L20.XCHPF IS NULL THEN NVL(L30.QUANTITY,0) ELSE 0 END l40.TOTALISSUEQTY,L20.CLABS,CASE WHEN L20.XCHPF='X' THEN NVL(L30.QUANTITY,0) ELSE 0 END l60.ISSUEQTY,L20.ERFME,L20.XCHPF,L20.VENDERCODE,L20.DATECODE,L20.AESZN,L20.T_SORTF,L20.RSNUM,L20.RSPOS
2 FROM (SELECT FACTORY,WO FROM APD_ADVICEBATCH_HEAD WHERE WO=:WO) L10 
3 JOIN (SELECT SID,WO,VORNR,KTSCH,PARTNO,MAKTX,WAREHOUSE,LGPBE,ERFMG,CLABS,ERFME,XCHPF,VENDERCODE,DATECODE,AESZN,T_SORTF,RSNUM,RSPOS  FROM APD_ADVICEBATCH_ITEM WHERE WO=:WO AND VORNR=:VORNR AND WAREHOUSE=:WAREHOUSE) L20 ON L10.WO=L20.WO 
4 LEFT JOIN (SELECT ABIID,SUM(QUANTITY) QUANTITY FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR AND WAREHOUSE=:WAREHOUSE GROUP BY ABIID) L30 ON L20.SID=L30.ABIID
5 LEFT JOIN (SELECT B.WO,B.VORNR,B.PARTNO,B.VENDERCODE,B.DATECODE,SUM(QUANTITY) TOTALISSUEQTY,B.RSPOS FROM (SELECT WO,VORNR,PARTNO,CASE XCHPF WHEN CAST('X' AS NVARCHAR2(1)) THEN VENDERCODE ELSE NULL END VENDERCODE, CASE XCHPF WHEN CAST('X' AS NVARCHAR2(1)) THEN DATECODE ELSE NULL END DATECODE,QUANTITY,RSPOS FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR) B GROUP BY B.WO,B.VORNR,B.PARTNO,B.VENDERCODE,B.DATECODE,B.RSPOS) L40 ON L20.WO=L40.WO AND L20.VORNR=L40.VORNR AND L20.PARTNO=L40.PARTNO AND L20.VENDERCODE=L40.VENDERCODE AND L20.DATECODE=L40.DATECODE AND L20.RSPOS=L40.RSPOS 
6 LEFT JOIN (SELECT C.WO,C.VORNR,C.PARTNO,C.VENDERCODE,C.DATECODE,C.RSPOS,SUM(QUANTITY) ISSUEQTY FROM (SELECT WO,VORNR,PARTNO,Case XCHPF WHEN CAST('X' as nvarchar2(1)) THEN VENDERCODE ELSE NULL END VENDERCODE, Case XCHPF WHEN CAST('X' as nvarchar2(1)) THEN DATECODE ELSE NULL END DATECODE,RSPOS,QUANTITY FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR AND (SAPUPLOAD=:SAPUPLOAD OR SAPUPLOAD IS NULL)) C GROUP BY C.WO,C.VORNR,C.PARTNO,C.VENDERCODE,C.DATECODE,C.RSPOS) L60 ON L20.WO=L60.WO AND L20.VORNR=L60.VORNR AND L20.PARTNO=L60.PARTNO AND L20.VENDERCODE=L60.VENDERCODE AND L20.DATECODE=L60.DATECODE AND L20.RSPOS=L60.RSPOS 
7 ORDER BY ROWNUM,l20.SID,L10.WO,L20.VORNR,L20.PARTNO,L20.WAREHOUSE,L20.LGPBE,L20.XCHPF,L20.VENDERCODE,L20.DATECODE
spc_net的主页 spc_net | 初学一级 | 园豆:186
提问于:2017-06-03 10:17
< >
分享
所有回答(1)
0

你这最好把报错信息发出来 ,这样才能具体分析

拓仲 | 园豆:268 (菜鸟二级) | 2017-06-03 11:08
1 Select ROWNUM,L20.SID,L10.FACTORY,L10.WO,L20.VORNR,L20.KTSCH,L20.PARTNO,L20.MAKTX,L20.WAREHOUSE,L20.LGPBE,L20.ERFMG,CASE WHEN L20.XCHPF IS NULL THEN NVL(L30.QUANTITY,0) ELSE 0 END L40.TOTALISSUEQTY,L20.CLABS,CASE WHEN L20.XCHPF='X' THEN NVL(L30.QUANTITY,0) ELSE 0 END L60.ISSUEQTY,L20.ERFME,L20.XCHPF,L20.VENDERCODE,L20.DATECODE,L20.AESZN,L20.T_SORTF,L20.RSNUM,L20.RSPOS,l30.QUANTITY 
2 FROM (SELECT FACTORY,WO FROM APD_ADVICEBATCH_HEAD WHERE WO=:WO) L10 
3 JOIN (SELECT SID,WO,VORNR,KTSCH,PARTNO,MAKTX,WAREHOUSE,LGPBE,ERFMG,CLABS,ERFME,XCHPF,VENDERCODE,DATECODE,AESZN,T_SORTF,RSNUM,RSPOS  FROM APD_ADVICEBATCH_ITEM WHERE WO=:WO AND VORNR=:VORNR AND WAREHOUSE=:WAREHOUSE) L20 ON L10.WO=L20.WO 
4 LEFT JOIN (SELECT ABIID,SUM(QUANTITY) QUANTITY FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR AND WAREHOUSE=:WAREHOUSE GROUP BY ABIID) L30 ON L20.SID=L30.ABIID
5 LEFT JOIN (SELECT B.ABIID,SUM(QUANTITY) TOTALISSUEQTYFROM,B.WO,B.VORNR,B.PARTNO,B.VENDERCODE,B.DATECODE,B.RSPOS FROM(SELECT ABIID,WO,VORNR,PARTNO,CASE XCHPF WHEN CAST('X' AS NVARCHAR2(1)) THEN VENDERCODE ELSE NULL END VENDERCODE, CASE XCHPF WHEN CAST('X' AS NVARCHAR2(1)) THEN DATECODE ELSE NULL END DATECODE,QUANTITY,RSPOS FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR) B GROUP BY B.ABIID,B.WO,B.VORNR,B.PARTNO,B.VENDERCODE,B.DATECODE,B.RSPOS) L40 ON L20.SID=L40.ABIID AND L20.WO=L40.WO AND L20.VORNR=L40.VORNR AND L20.PARTNO=L40.PARTNO AND L20.VENDERCODE=L40.VENDERCODE AND L20.DATECODE=L40.DATECODE AND L20.RSPOS=L40.RSPOS
6 LEFT JOIN (SELECT C.ABIID,C.WO,C.VORNR,C.PARTNO,C.VENDERCODE,C.DATECODE,C.RSPOS,SUM(QUANTITY) ISSUEQTY FROM (SELECT ABIID,WO,VORNR,PARTNO,Case XCHPF WHEN CAST('X' as nvarchar2(1)) THEN VENDERCODE ELSE NULL END VENDERCODE, Case XCHPF WHEN CAST('X' as nvarchar2(1)) THEN DATECODE ELSE NULL END DATECODE,RSPOS,QUANTITY FROM APD_POSTMATERIAL_ITEM WHERE WO=:WO AND VORNR=:VORNR AND (SAPUPLOAD=:SAPUPLOAD OR SAPUPLOAD IS NULL)) C GROUP BY C.ABIID,C.WO,C.VORNR,C.PARTNO,C.VENDERCODE,C.DATECODE,C.RSPOS) L60 ON L20.SID=L60.ABIID AND L20.WO=L60.WO AND L20.VORNR=L60.VORNR AND L20.PARTNO=L60.PARTNO AND L20.VENDERCODE=L60.VENDERCODE AND L20.DATECODE=L60.DATECODE AND L20.RSPOS=L60.RSPOS 
7 ORDER BY ROWNUM,l20.SID,L10.WO,L20.VORNR,L20.PARTNO,L20.WAREHOUSE,L20.LGPBE,L20.XCHPF,L20.VENDERCODE,L20.DATECODE

 

支持(0) 反对(0) spc_net | 园豆:186 (初学一级) | 2017-06-03 11:23

@spc_net: 你的工具下面不是提示你 10行184列 你看看是什么字段 把字段去了 在看看是否报错

支持(0) 反对(0) 拓仲 | 园豆:268 (菜鸟二级) | 2017-06-03 11:40

@养乐多1号:  TOTALISSUEQTY ISSUEQTY这俩字段前面分别加了一个别名 L40,L60,把别名去了就没不报错,但是这俩字段拿不到值。但是下面的left join 右边又能拿到。所以我怀疑是我的 case when语句错了

支持(0) 反对(0) spc_net | 园豆:186 (初学一级) | 2017-06-03 11:48
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册