Azure Synapse error： "Some part of your SQL statement is nested too deeply. Rewrite the query or bre

Question

我得到的SQL语句的某些部分嵌套得太深。重写查询或将其分解为更小的查询。当我运行这段代码时出现错误。

SELECT  sl.CUSTACCOUNT, ct.INVOICEACCOUNT, saest.ENUMITEMLABEL,
(sl.LINEAMOUNT + case when mt.markupvalue is null then 0 
when mt.markupcategory = 0 then  mt.markupvalue   --Fixed
when mt.markupcategory = 1 then mt.markupvalue * sl.qtyordered   -- per qty
when mt.markupcategory = 2 then mt.markupvalue * sl.qtyordered /100  -- percent
when mt.markupcategory = 3 then mt.markupvalue * sl.qtyordered /100  -- Inter Company Percent
else 9999999999 -- show as high number to denote a problem
end 
+ case when cist.additivevalue is null then 0 else (cist.additivevalue * sl.qtyordered) end
+ ((sl.LINEAMOUNT +  case when mt.markupvalue is null then 0  
when mt.markupcategory = 0 then  mt.markupvalue   --Fixed
when mt.markupcategory = 1 then mt.markupvalue * sl.qtyordered   -- per qty
when mt.markupcategory = 2 then mt.markupvalue * sl.qtyordered /100  -- percent
when mt.markupcategory = 3 then mt.markupvalue * sl.qtyordered /100 else -- Inter Company Percent
9999999999 -- show as high number to denote a problem
end 
+ (case when cist.additivevalue is null then 0 else (cist.additivevalue * sl.qtyordered) end))/ 100 * case when TaxValue.TAXVALUE is null then 0 else taxvalue.taxvalue end)) 
as InclGST
FROM 
table1 as sl 
INNER JOIN  table2       AS st    ON sl.SALESID = st.SALESID AND sl.DATAAREAID = st.DATAAREAID 
INNER JOIN  table3        AS ct    ON sl.CUSTACCOUNT = ct.accountnum AND sl.DATAAREAID =ct.DATAAREAID  
INNER JOIN  table4 AS saest  ON saest.ENUMID   = '276' AND saest.ENUMITEMVALUE = st.SALESSTATUS 
left outer join (select tgd.taxgroup,td.TAXCODE,td.taxvalue,td.dataareaid from table5 as td 
inner join table6 as tgd on tgd.taxcode=td.taxcode and tgd.dataareaid=td.dataareaid where td.taxcode <> 'Capital' and td.taxvalue <> '0')as TaxValue
on sl.taxgroup =TaxValue.taxgroup and sl.dataareaid=TaxValue.Dataareaid 
left outer join (select mts.transrecid, mts.dataareaid, mts.MARKUPCATEGORY, sum(mts.value) as MarkupValue from table7 as mts
--where mts.transtableid = '359'
where mts.transtableid = '15754'  --Bhaskar 07/06/21--
group by mts.transrecid, mts.dataareaid, mts.MARKUPCATEGORY ) as mt
on mt.transrecid = sl.recid and mt.dataareaid = sl.dataareaid  
left outer join (select  rcist.refrecid, rcist.additive,rcist.dataareaid, sum(rcist.salesprice*rcist.qty) as AdditiveValue 
from table8 as rcist
-- where rcist.additive = '1' and rcist.reftableid = '359'
where rcist.additive = '1' and rcist.reftableid = '15754' -- Bhaskar 07/06/21--
group by rcist.refrecid, rcist.additive, rcist.dataareaid 
) as cist 
on cist.refrecid = sl.recid and cist.dataareaid = sl.dataareaid
WHERE
(NOT (st.SALESSTATUS = 4)) AND (sl.SALESSTATUS = 1 OR  sl.SALESSTATUS = 2) AND (NOT (st.SALESTYPE = 5))

我尝试为最后3个左外连接创建临时表，但我仍然面临同样的问题。

有趣的是，这个查询似乎并不太复杂或嵌套。我正在运行另一个8连接查询，这似乎工作得很好。我确实面临着同样的问题，但没有这个复杂。

这可能是因为这里提到的所有表(table1,table2，…table8)都是建立在基表之上的视图(不是很复杂)吗?

Answer 1

我用COALESCE函数替换了case语句，并删除了SQL子查询中不必要的嵌套。这是未经测试的版本。请进行必要的修改以使其正常工作

WITH TaxValues AS (
SELECT tgd.taxgroup, td.TAXCODE, td.taxvalue, td.dataareaid 
FROM table5 AS td 
INNER JOIN table6 AS tgd ON tgd.taxcode = td.taxcode AND tgd.dataareaid = td.dataareaid 
WHERE td.taxcode <> 'Capital' AND td.taxvalue <> '0'
),
MarkupValues AS (
SELECT mts.transrecid, mts.dataareaid, mts.MARKUPCATEGORY, SUM(mts.value) as MarkupValue 
FROM table7 AS mts
WHERE mts.transtableid = '15754'
GROUP BY mts.transrecid, mts.dataareaid, mts.MARKUPCATEGORY 
),
AdditiveValues AS (
SELECT rcist.refrecid, rcist.additive, rcist.dataareaid, SUM(rcist.salesprice*rcist.qty) as AdditiveValue 
FROM table8 AS rcist
WHERE rcist.additive = '1' AND rcist.reftableid = '15754'
GROUP BY rcist.refrecid, rcist.additive, rcist.dataareaid 
)
SELECT 
sl.CUSTACCOUNT,
ct.INVOICEACCOUNT,
saest.ENUMITEMLABEL,
(sl.LINEAMOUNT
+ COALESCE(mt.markupvalue * CASE mt.markupcategory 
WHEN 1 THEN sl.qtyordered 
WHEN 2 THEN sl.qtyordered / 100 
WHEN 3 THEN sl.qtyordered / 100 
ELSE 1 
END, 0)
+ COALESCE(cist.additivevalue * sl.qtyordered, 0)
+ ((sl.LINEAMOUNT
+ COALESCE(mt.markupvalue * CASE mt.markupcategory 
WHEN 1 THEN sl.qtyordered 
WHEN 2 THEN sl.qtyordered / 100 
WHEN 3 THEN sl.qtyordered / 100 
ELSE 1 
END, 0)
+ COALESCE(cist.additivevalue * sl.qtyordered, 0)) / 100 
* COALESCE(taxvalue.taxvalue, 0)
) as InclGST
FROM 
table1 AS sl 
INNER JOIN table2 AS st ON sl.SALESID = st.SALESID AND sl.DATAAREAID = st.DATAAREAID 
INNER JOIN table3 AS ct ON sl.CUSTACCOUNT = ct.accountnum AND sl.DATAAREAID = ct.DATAAREAID  
INNER JOIN table4 AS saest ON saest.ENUMID = '276' AND saest.ENUMITEMVALUE = st.SALESSTATUS 
LEFT OUTER JOIN TaxValues AS TaxValue ON sl.taxgroup = TaxValue.taxgroup AND sl.dataareaid = TaxValue.Dataareaid 
LEFT OUTER JOIN MarkupValues AS mt ON mt.transrecid = sl.recid AND mt.dataareaid = sl.dataareaid  
LEFT OUTER JOIN AdditiveValues AS cist ON cist.refrecid = sl.recid AND cist.dataareaid = sl.dataareaid
WHERE
NOT (st.SALESSTATUS = 4) 
AND (sl.SALESSTATUS = 1 OR sl.SALESSTATUS = 2) 
AND NOT (st.SALESTYPE = 5)