try:SELECT tblPODet.PartCode,tblStock.PartRule,tblPODet.BatchNo,
tblStock.Unit, (tblstock.Unit) AS SpareUnit,
tblStock.IsTaxRemit,tblStock.WHCode
FROM tblStock,tblPODet
WHERE tblPODet.PONo='PO20020802002'
AND tblPODet.PartCode=tblStock.PartCode
AND tblPODet.IsClosed=0
ORDER BY
tblPODet.PartCode,tblPODet.BatchNo,
tblStock.Unit
tblStock.Unit, (tblstock.Unit) AS SpareUnit,
tblStock.IsTaxRemit,tblStock.WHCode
FROM tblStock,tblPODet
WHERE tblPODet.PONo='PO20020802002'
AND tblPODet.PartCode=tblStock.PartCode
AND tblPODet.IsClosed=0
ORDER BY
tblPODet.PartCode,tblPODet.BatchNo,
tblStock.Unit
沒辦法,我的命總是這麼苦,被老板剝削還不算,干點事情還總是被Gates折磨。
極度的郁悶啊!!
「三」的意思是
新建一個資料庫,然後把tblStock、tblPODet的結構(連同資料)複製到新建的資料庫中,然後在新建的資料庫里面,執行我上面的SQL,這樣則不會有錯誤。之所以把我的步驟帖出來,是因為我怕我講的不夠明白,所以干脆把步驟的SQL帖出來了。TO 海兄:
試過了,結果還是不能執行。嗯剛剛發現還有第四、五種情況,(好像還更奇怪)
「四」
打開Enterprise Manager,右擊tblStock,選擇Open Table\Return All rows, 然後點擊工具欄上的"Show/Hide SQL Plan"按鈕,然後把上面的SQL帖上來,點Run,哈哈,結果是可以執行,不會出錯!!!
在Enterprise Manager中,點View\New View,然後把SQL 帖到SQL Plan中,點Run,這時會自動把我的SQL 變成SELECT TOP 100 PERCENT dbo.tblPODet.PartCode, dbo.tblStock.PartRule,
dbo.tblPODet.BatchNo, dbo.tblStock.Unit, dbo.tblStock.Unit AS SpareUnit,
dbo.tblStock.IsTaxRemit, dbo.tblStock.WHCode
FROM dbo.tblStock INNER JOIN
dbo.tblPODet ON dbo.tblStock.PartCode = dbo.tblPODet.PartCode
WHERE (dbo.tblPODet.IsClosed = 0) AND (dbo.tblPODet.PONo = 'PO20020802002')
ORDER BY dbo.tblPODet.PartCode, dbo.tblPODet.BatchNo, dbo.tblStock.Unit
--這樣執行不會有誤。把view保存成viwTest.
再打開SQL Query Analyzer,輸入SELECT * FROM viwTest--結果還是會出現:
Server: Msg 8623, Level 16, State 1, Line 1
Internal Query Processor Error: The query processor could not produce a query plan. Contact your primary support provider for more information.
歷害!!!其實重點就在這里,呵呵。
tblStock的Primary Key是PartCode欄位,
tblPODet的Primary Key是PONo,PartCode、Batch三個欄位。若是把tblPODet或是tblStock的Primary Key限制拿掉的話,就不會報錯!!!但是,
在不拿掉Primary Key的情況下,把SELECT語句里面的tblPODet.PartCode換成
tblStock.PartCode,(其它的不變),
同樣也不會報錯!!!
自动优化的问题.
1.ORDER BY 1,3,4
2.Yang_(扬帆破浪) 原来就是海兄
没有结论,但找到一些避免的方法。to:supsuccess(口气不小)
有一阵好久没见你,没和你说,不好意思!呵呵