SQL Server Error thrown while executing query : Msg 8618

dfddblmv 于 2023-03-17 发布在其他

关注(0)|答案(2)|浏览(154)

I am using a table three times in a query based on different selection:

Select *
from
    (Select * from report where source = 'FA') FA
left join 
    (Select * from report where source = 'PI') PI
          on FA.account = PI.account and FA.Dealer = PI.Dealer
left join  
    (Select * from report where source = 'PIM') PIM
          on FA.account = PIM.account and FA.Dealer = PIM.Dealer

As the data is big in volume, I am getting the following error:

Msg 8618, Level 16, State 2, Line 3
The query processor could not produce a query plan because a worktable is required, and its minimum row size exceeds the maximum allowable of 8060 bytes. A typical reason why a worktable is required is a GROUP BY or ORDER BY clause in the query. If the query has a GROUP BY or ORDER BY clause, consider reducing the number and/or size of the fields in the clause. Consider using prefix (LEFT()) or hash (CHECKSUM()) of fields for grouping or prefix for ordering. Note however that this will change the behavior of the query.

Is there any solution?

sql-server

来源：https://stackoverflow.com/questions/66891900/error-thrown-while-executing-query-msg-8618

2条答案

按热度按时间

xqkwcwgp1#

Not sure what you want without the columns and schemas; but ...

Try something like this:

SELECT 
        RPT.<
                ... ListOfCommonColumns here
            >
    ,   RFA.<
                ... RFA Columns here
            >   
    ,   RPI.<
                ... RPI Columns here
            >   
    ,   PIM.<
                ... PIM Columns here
            >   

FROM        Report  RPT

LEFT JOIN   Report  RFA ON  RFA.<key>   =   RPT.<key>
                        AND RFA.Source  =   'FA'
LEFT JOIN   Report  RPI ON  RPI.<key>   =   RPT.<key>
                        AND RPI.Source  =   'PI'
LEFT JOIN   Report  PIM ON  PIM.<key>   =   RPT.<key>
                        AND PIM.Source  =   'PI'
                        AND RPT.Account =   PIM.Account 
                        AND RFA.Dealer  =   PIM.Dealer

-- I am not sure, but have the impresion that <key> is a combination of Account and Dealer

赞(0）回复(0）举报 2023-03-17

xe55xuns2#

In my research I found the issue was actually more related to a UNION and a SELECT DISTINCT, example below where TABLE1 and TABLE2 values match but have distinct record sets.

WITH A AS (SELECT * FROM JOINTABLE)
SELECT *
FROM 
(
SELECT DISTINCT * 
FROM TABLE1 
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE1.COLUMN1
UNION
SELECT DISTINCT * FROM TABLE2
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE2.COLUMN1
)

Changing the union to be a UNION ALL and removing the DISTINCT for other methods helped me.

WITH A AS (SELECT * FROM JOINTABLE)
SELECT *
FROM 
(
SELECT * 
FROM TABLE1 
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE1.COLUMN1
UNION ALL
SELECT *
FROM TABLE2
LEFT JOIN JOINTABLE ON
JOINTABLE.COLUMN1 = TABLE2.COLUMN1
)

Edit: Eventually I had to rewrite the entire query to have better syntax.

赞(0）回复(0）举报 2023-03-17

我来回答

SQL Server Error thrown while executing query : Msg 8618

2条答案

相关问题

热门标签

最新问答