# 关于 case when语句

0

```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
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 | 初学一级 | 园豆：186

0

```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
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```

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

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

您需要登录以后才能回答，未注册用户请先注册