「暫存SQL碼」:修訂間差異
| 第19行: | 第19行: | ||
JOIN BOMMD ON MD001=TD004 | JOIN BOMMD ON MD001=TD004 | ||
WHERE TD021='Y' AND MD003 LIKE 'TSS-283%' | WHERE TD021='Y' AND MD003 LIKE 'TSS-283%' | ||
</SQL> | |||
==查詢逾期未交之訂單及金額== | |||
<SQL> | |||
SELECT TC075, | |||
(SELECT CASE WHEN COPTD.UDF02<>'' THEN COPTD.UDF02 ELSE TC012 END)TT, | |||
TD013,TD001,TD002,TD003,TD004,TD008,ISNULL(CAST(TA015 AS CHAR),'')TA015,ISNULL(CAST(TA017 AS CHAR),'')TA017,TD009, | |||
TD008-TD009 AS DF008,TD011*(TD008-TD009)*TC009 AS DFMONEY | |||
FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 | |||
JOIN COPMA ON MA001=TC004 | |||
LEFT JOIN MOCTA ON TA001='5102' AND TA002=TD054 AND TA013='Y' | |||
WHERE MA019>=401 AND TD016='N' AND TD021='Y' AND TD013<'20170400' AND TC004<>'0518' AND TD013<'20170101' AND TC004<>'N306' | |||
ORDER BY TC075,TC013,TD001,TD002,TD004 | |||
</SQL> | </SQL> | ||
於 2017年4月21日 (五) 10:24 的修訂
領料單列印
- 20160514 以PDO取值
<sql> SELECT DENSE_RANK() OVER (ORDER BY TE002) AS ROWID ,X.*,TB004,CAST(TB004/TA015 AS FLOAT) AS EACHQ FROM (SELECT TA001,TA002,TD005,TE001,TE002,TE003,TE004,TE017,TA015,TE005,TD013,TA520,TA526, TC004,TE019,TA011,TA013,TE010,TE011,TE012,TC075 AS MA002 FROM MOCTA JOIN MOCTE ON TA001=TE011 AND TA002=TE012 JOIN INVMB ON MB001=TE004 JOIN v_COPTCD ON TA001='5102' AND TA002=TD054 WHERE TE001= '5403' AND MB007<>'302' AND TA001='5102' AND TA002 LIKE '%150811006%')X JOIN MOCTB ON TB001=TE011 AND TB002=TE012 AND TB003=TE004 WHERE TE019<>'V' ORDER BY TE004 </SQL>
查詢訂單內含有某電線
<SQL> SELECT TC003,TD004,TD005,TD006,TD041,MD003,TD008,MD006/MD007*TD008 AS AMOUNT FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN BOMMD ON MD001=TD004 WHERE TD021='Y' AND MD003 LIKE 'TSS-283%' </SQL>
查詢逾期未交之訂單及金額
<SQL> SELECT TC075, (SELECT CASE WHEN COPTD.UDF02<> THEN COPTD.UDF02 ELSE TC012 END)TT, TD013,TD001,TD002,TD003,TD004,TD008,ISNULL(CAST(TA015 AS CHAR),)TA015,ISNULL(CAST(TA017 AS CHAR),)TA017,TD009,
TD008-TD009 AS DF008,TD011*(TD008-TD009)*TC009 AS DFMONEY
FROM COPTD JOIN COPTC ON TC001=TD001 AND TC002=TD002 JOIN COPMA ON MA001=TC004 LEFT JOIN MOCTA ON TA001='5102' AND TA002=TD054 AND TA013='Y' WHERE MA019>=401 AND TD016='N' AND TD021='Y' AND TD013<'20170400' AND TC004<>'0518' AND TD013<'20170101' AND TC004<>'N306' ORDER BY TC075,TC013,TD001,TD002,TD004 </SQL>