关于 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
所有回答(1)
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 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
@spc_net: 你的工具下面不是提示你 10行184列 你看看是什么字段 把字段去了 在看看是否报错
@养乐多1号: TOTALISSUEQTY ISSUEQTY这俩字段前面分别加了一个别名 L40，L60，把别名去了就没不报错，但是这俩字段拿不到值。但是下面的left join 右边又能拿到。所以我怀疑是我的 case when语句错了
