「台康來料追蹤」:修訂間差異
| 第15行: | 第15行: | ||
JOIN MOCTB ON TA001=TB001 AND TA002=TB002 | JOIN MOCTB ON TA001=TB001 AND TA002=TB002 | ||
WHERE TA034 LIKE '518A%' AND TA013<>'V' AND (TA011<>'Y' AND TA011<>'y') AND TB011='1' AND CHARINDEX(SUBSTRING(TB012,6,100), TD020)>1)X | WHERE TA034 LIKE '518A%' AND TA013<>'V' AND (TA011<>'Y' AND TA011<>'y') AND TB011='1' AND CHARINDEX(SUBSTRING(TB012,6,100), TD020)>1)X | ||
LEFT JOIN MOCTE ON TA001=TE011 AND TA002=TE012 AND TB003=TE004 AND TE005 >0 | |||
</sql> | |||
==v_518A== | |||
<sql> | |||
SELECT TA011,PSTD,TA001,TA002,TA526,TA521,TA006,TA034,TB003,TB012,UNGET,SMC007,SMC007-UNGET AS CK,TE001,TE002,TE003 | |||
FROM(SELECT TA001,TA002,TA006,TA034,TA526,TA521,TD020,TB003,TB012,TA011,CAST(MOCTA.UDF06 AS INT) AS PSTD,TB004-TB005 AS UNGET FROM MOCTA | |||
JOIN v_COPTCD ON TA001='5102' AND TA002=TD054 | |||
JOIN MOCTB ON TA001=TB001 AND TA002=TB002 | |||
WHERE TA034 LIKE '518A%' AND TA013<>'V' AND (TA011<>'Y' AND TA011<>'y') AND TB011='1' AND CHARINDEX(SUBSTRING(TB012,6,100), TD020)>1)X | |||
JOIN v_INVMC ON MC001=TB003 | |||
LEFT JOIN MOCTE ON TA001=TE011 AND TA002=TE012 AND TB003=TE004 AND TE005 >0 | LEFT JOIN MOCTE ON TA001=TE011 AND TA002=TE012 AND TB003=TE004 AND TE005 >0 | ||
</sql> | </sql> | ||
於 2016年4月27日 (三) 14:06 的最新修訂
程式碼(以BOM)
<sql> SELECT TA001,TA002,TA006,TA034,TA035,TA013,TA510,TA520,TA522,TA524,TA526,TA011 FROM (SELECT MD001 FROM (SELECT TB001,TB002,TB003,TB004,TB005,TB006,TB007,TB018,MD001 FROM INVTB JOIN BOMMD ON MD003=TB004 WHERE TB001='1105' AND TB004 LIKE '60518%' AND TB018='Y') X GROUP BY MD001)Y JOIN MOCTA ON TA006=MD001 JOIN MOCTE ON TA001=TE011 AND TA002=TE012 WHERE TE019='Y' AND TA013<>'V' AND TA011<>'Y' AND TA011<>'y' </sql>
程式碼(以MOCTB&E)
<sql> SELECT TA011,PSTD,TA001,TA002,TA526,TA521,TA006,TA034,TE001,TE002,TE003,TE004,TE017,TB012 FROM(SELECT TA001,TA002,TA006,TA034,TA526,TA521,TD020,TB003,TB012,TA011,CAST(MOCTA.UDF06 AS INT) AS PSTD FROM MOCTA JOIN v_COPTCD ON TA001='5102' AND TA002=TD054 JOIN MOCTB ON TA001=TB001 AND TA002=TB002 WHERE TA034 LIKE '518A%' AND TA013<>'V' AND (TA011<>'Y' AND TA011<>'y') AND TB011='1' AND CHARINDEX(SUBSTRING(TB012,6,100), TD020)>1)X LEFT JOIN MOCTE ON TA001=TE011 AND TA002=TE012 AND TB003=TE004 AND TE005 >0 </sql>
v_518A
<sql> SELECT TA011,PSTD,TA001,TA002,TA526,TA521,TA006,TA034,TB003,TB012,UNGET,SMC007,SMC007-UNGET AS CK,TE001,TE002,TE003 FROM(SELECT TA001,TA002,TA006,TA034,TA526,TA521,TD020,TB003,TB012,TA011,CAST(MOCTA.UDF06 AS INT) AS PSTD,TB004-TB005 AS UNGET FROM MOCTA JOIN v_COPTCD ON TA001='5102' AND TA002=TD054 JOIN MOCTB ON TA001=TB001 AND TA002=TB002 WHERE TA034 LIKE '518A%' AND TA013<>'V' AND (TA011<>'Y' AND TA011<>'y') AND TB011='1' AND CHARINDEX(SUBSTRING(TB012,6,100), TD020)>1)X JOIN v_INVMC ON MC001=TB003 LEFT JOIN MOCTE ON TA001=TE011 AND TA002=TE012 AND TB003=TE004 AND TE005 >0 </sql>