「ERP Debug SQL code」:修訂間差異
跳至導覽
跳至搜尋
| 第8行: | 第8行: | ||
==已發製令未建BOM查詢== | ==已發製令未建BOM查詢== | ||
<sql> | <sql> | ||
SELECT TA001,TA002,AMOUNT,TA034 FROM MOCTA | SELECT TA001,TA002,TA004,AMOUNT,TA034,TA035,TA013,TA011,TA010 FROM MOCTA | ||
LEFT JOIN | LEFT JOIN | ||
(SELECT TB002,COUNT(TB003) AS AMOUNT FROM MOCTB WHERE TB001='5102' AND TB003<>'Z001' AND TB002 >='151102001' | (SELECT TB002,COUNT(TB003) AS AMOUNT FROM MOCTB WHERE TB001='5102' AND TB003<>'Z001' AND TB002 >='151102001' | ||
GROUP BY TB002)X ON TA002=TB002 WHERE TA001='5102' AND TA002 >='151102001' ORDER BY AMOUNT | GROUP BY TB002)X ON TA002=TB002 WHERE TA001='5102' AND TA002 >='151102001' ORDER BY AMOUNT | ||
</sql> | </sql> | ||
於 2015年11月10日 (二) 03:20 的修訂
訂單品號與製令品號不符SQL碼
<sql> SELECT TA011,TC006,TC004,TD001,TD002,TD003,TA001,TA002,TD004,TA006,TD005,TA034,TD006,TA035 FROM COPTD JOIN MOCTA ON TD001=TA026 AND TD002=TA027 AND TD003=TA028 JOIN COPTC ON TD001=TC001 AND TD002=TC002 WHERE TA011<>'V' AND TA011<>'Y' AND TD004<>TA006 AND TA013<>'V' AND TA006 NOT LIKE'53%' ORDER BY TC006 </sql>
已發製令未建BOM查詢
<sql> SELECT TA001,TA002,TA004,AMOUNT,TA034,TA035,TA013,TA011,TA010 FROM MOCTA LEFT JOIN (SELECT TB002,COUNT(TB003) AS AMOUNT FROM MOCTB WHERE TB001='5102' AND TB003<>'Z001' AND TB002 >='151102001' GROUP BY TB002)X ON TA002=TB002 WHERE TA001='5102' AND TA002 >='151102001' ORDER BY AMOUNT </sql>