SQL Server Azure Synapse error: "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

gopyfrb3 于 2023-04-19 发布在其他

关注(0)|答案(1)|浏览(104)

I'm getting the "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries." error when I'm running this piece of code.

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))

I tried creating temp tables for the last 3 left outerjoins but I'm still facing the same issue.

Funny part is, this query doesn't seem too complicated or nested. I'm running another query with 8 joins and that seems to work just fine. I'm literally facing the same issues which are less complex than this.

Could this be because all my tables mentioned here (table1,table2,...table8) are views (not very complex) which are built on top of base tables?

sql-server

来源：https://stackoverflow.com/questions/75939959/azure-synapse-error-some-part-of-your-sql-statement-is-nested-too-deeply-rewr

1条答案

按热度按时间

bjg7j2ky1#

I've replaced the case statements with COALESCE function and removed the unnecessary nesting of the SQL subqueries.It's the untested version.Please make the necessary changes to make it work

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)

赞(0）回复(0）举报 2023-04-19

我来回答

SQL Server Azure Synapse error: "Some part of your SQL statement is nested too deeply. Rewrite the query or break it up into smaller queries."

1条答案

相关问题

热门标签

最新问答