「台康來料追蹤」:修訂間差異
新頁面: ==程式碼== <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,MD00... |
|||
| (未顯示同一使用者於中間所作的 1 次修訂) | |||
| 第1行: | 第1行: | ||
==程式碼== | ==程式碼(以BOM)== | ||
<sql> | <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 | 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 | ||
| 第7行: | 第7行: | ||
JOIN MOCTE ON TA001=TE011 AND TA002=TE012 | JOIN MOCTE ON TA001=TE011 AND TA002=TE012 | ||
WHERE TE019='Y' AND TA013<>'V' AND TA011<>'Y' AND TA011<>'y' | 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> | </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>