TIPTOP 自訂義查詢報表問題

tiptop 自訂義查詢報表問題

chare168 2021-03-18 09:05:46 ‧ 876 瀏覽

分享至

請教我在自訂查詢報表語法
SELECT sfa01,sfa03,sfa05,sfa06,sfa062,sfa063,sfa16,sfb09,a.tlf10 as 領料量,a.tlf10 - sfa063 as 領料量減報廢量,sfa06+sfa062-sfa063 -( a.tlf10 - sfa063)as 結存
FROM sfa_file
LEFT OUTER JOIN sfb_file
ON sfa01 = sfb01
LEFT OUTER JOIN
(SELECT DISTINCT tlf01,tlf62,sum( tlf10 * tlf907 * -1 )tlf10 FROM tlf_file
WHERE tlf13 MATCHES 'asfi5*'
AND tlf907 <> 0
GROUP BY tlf01,tlf62 ) a
ON sfa03 = a.tlf01
WHERE tlf01 = sfa03 AND tlf62 = sfa01 and sfb02 = '7' and sfb87 = 'Y' and ( sfb04 <> '8' or sfb04 <> '1' ) AND sfb13 >= '2020/01/01'
直接查詢資料是可以正常查詢資料
可是執行程式編號填為填入料號或工單號或...(任意欄位) 查詢就會出錯
未發現表格中有欄位column-name (或SLV尚未加以定義)
請問要如何修正
謝謝

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

souda

iT邦高手 1 級 ‧ 2021-03-19 08:18:22

你的sql結構有問題!我已修正了你再試看看.
SELECT a.sfa01,a.sfa03,a.sfa05,a.sfa06,a.sfa062,a.sfa063,a.sfa16,b.sfb09,c.tlf10,c.tlf10 - a.sfa063,a.sfa06+a.sfa062-a.sfa063 -( c.tlf10 - a.sfa063)
FROM sfa_file a
LEFT OUTER JOIN sfb_file b ON a.sfa01 = b.sfb01
LEFT OUTER JOIN (SELECT DISTINCT tlf01,tlf62,sum( tlf10 * tlf907 * -1 )tlf10 FROM tlf_file WHERE tlf13 MATCHES 'asfi5*' AND tlf907 <> 0 GROUP BY tlf01,tlf62 ) c ON a.sfa03 = c.tlf01
WHERE c.tlf01 = a.sfa03 AND c.tlf62 = a.sfa01 and b.sfb02 = '7' and b.sfb87 = 'Y' and ( b.sfb04 <> '8' or b.sfb04 <> '1' ) AND b.sfb13 >= '2020/01/01'