expression services limit has been reached

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

expression services limit has been reached

Author

Topic

osupratt
Posting Yak Master

238 Posts

Posted - 2008-10-21 : 12:37:07

I have a query that is basically determining a value and then a sub-report based upon that value to determine another value and a sub-report upon that, etc, etc.

Is there a better way to do this (I'm not very versed yet in SQL)? Here is my query. I can run up to the 4th SELECT statement, but when I try on the last or the 5th it comes back with the 'Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.'

Here is the query. Any help would be greatly appreciated. Thanks.:

SELECT
ItemNumber, ItemDescription, QtyOnHand, MonthsSupply, QtySalesYear1,QtySalesYear2,QtySalesYear3,QtySalesYear4,
CASE WHEN (QtyOnHand -QtySalesYear1-QtySalesYear2-QtySalesYear3-QtySalesYear4-AvgSalesYear)>0 THEN AvgSalesYear
ELSE QtyOnHand-QtySalesYear1-QtySalesYear2-QtySalesYear3-QtySalesYear4 END AS QtySalesYear5

FROM(

SELECT
ItemNumber, ItemDescription, QtyOnHand, MonthsSupply, QtySalesYear1,QtySalesYear2,QtySalesYear3,
CASE WHEN (QtyOnHand -QtySalesYear1-QtySalesYear2-QtySalesYear3-AvgSalesYear)>0 THEN AvgSalesYear
ELSE (QtyOnHand-QtySalesYear1-QtySalesYear2-QtySalesYear3) END AS QtySalesYear4

FROM(

SELECT
ItemNumber, ItemDescription, QtyOnHand, MonthsSupply, QtySalesYear1,QtySalesYear2,
CASE WHEN (QtyOnHand -QtySalesYear1-QtySalesYear2-AvgSalesYear)>0 THEN AvgSalesYear
ELSE (QtyOnHand-QtySalesYear1-QtySalesYear2) END AS QtySalesYear3

FROM(

SELECT
ItemNumber, ItemDescription, QtyOnHand, MonthsSupply, QtySalesYear1,
CASE WHEN (QtyOnHand -QtySalesYear1-AvgSalesYear)>0 THEN AvgSalesYear
ELSE (QtyOnHand-QtySalesYear1) END AS QtySalesYear2

FROM(

SELECT
ItemNumber, ItemDescription, QtyOnHand,
CASE WHEN AvgSalesYear=0 THEN 987654321 ELSE QtyOnHand/(AvgSalesYear/12) END AS MonthsSupply,
CASE WHEN (QtyOnHand - AvgSalesYear)>0 THEN AvgSalesYear ELSE QtyOnHand END AS QtySalesYear1

FROM(

SELECT

ItemNumber, ItemDescription, QtyOnHand,
CASE WHEN ISNULL(firstin.ACTIVEMONTHS, 0) = 0 OR ISNULL(sold.QtySoldPeriod, 0) = 0 THEN 0
ELSE ROUND((ISNULL(sold.QtySoldPeriod, 0) / ISNULL(firstin.ACTIVEMONTHS, 0)) * 12, 0) END AS AvgSalesYear

FROM VW_RTMS_BAS_SteveFirstIn_1 AS firstin LEFT OUTER JOIN
VW_RTMS_BAS_SteveInvSold_1 AS sold ON firstin.item_id = sold.item LEFT OUTER JOIN
VW_RTMS_BAS_SteveQtyOnHand_1 AS qtyonhand ON firstin.item_id = qtyonhand.item
WHERE (qtyonhand.QtyOnHand > 0)

) AS A
) AS B
) AS C
) AS D
) AS E

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 14:18:42

[code];with test1 (ItemNumber, ItemDescription, QtyOnHand, AvgSalesYear, monthsupply, qtysalesyear, start, endyr) as (
SELECT
ItemNumber, ItemDescription, QtyOnHand, AvgSalesYear,
CASE WHEN AvgSalesYear=0 THEN 987654321 ELSE QtyOnHand/(AvgSalesYear/12) END AS MonthsSupply,
CASE WHEN (QtyOnHand - AvgSalesYear)>0 THEN AvgSalesYear ELSE QtyOnHand END AS QtySalesYear, 1 as start, 5 as endyr
FROM(
SELECT

ItemNumber, ItemDescription, QtyOnHand,
CASE WHEN ISNULL(firstin.ACTIVEMONTHS, 0) = 0 OR ISNULL(sold.QtySoldPeriod, 0) = 0 THEN 0
ELSE ROUND((ISNULL(sold.QtySoldPeriod, 0) / ISNULL(firstin.ACTIVEMONTHS, 0)) * 12, 0) END AS AvgSalesYear

FROM VW_RTMS_BAS_SteveFirstIn_1 AS firstin LEFT OUTER JOIN
VW_RTMS_BAS_SteveInvSold_1 AS sold ON firstin.item_id = sold.item LEFT OUTER JOIN
VW_RTMS_BAS_SteveQtyOnHand_1 AS qtyonhand ON firstin.item_id = qtyonhand.item
WHERE (qtyonhand.QtyOnHand > 0)
)a
UNION ALL select
ItemNumber, ItemDescription, QtyOnHand-QtySalesYear, AvgSalesYear,monthsupply,
CASE WHEN (QtyOnHand -QtySalesYear-AvgSalesYear)>0 THEN AvgSalesYear
ELSE (QtyOnHand-QtySalesYear) END AS QtySalesYear, start+1, endyr from test1
where start <= endyr
)
select
ItemNumber, ItemDescription, max(qtyonhand), monthsupply, AvgSalesYear,
max(case when start = 1 then isnull(QtySalesYear ,0) end) as year1,
max(case when start = 2 then isnull(QtySalesYear ,0) end) as year2,
max(case when start = 3 then isnull(QtySalesYear ,0) end) as year3,
max(case when start = 4 then isnull(QtySalesYear ,0) end) as year4,
max(case when start = 5 then isnull(QtySalesYear ,0) end) as year5
from test1
group by ItemNumber, ItemDescription, monthsupply, AvgSalesYear[/code]

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-21 : 14:36:31

To increase the number of the year change ENDYR to whatever. Then add a few MAX(Case when start = ... ) as yearx

osupratt
Posting Yak Master

238 Posts

Posted - 2008-10-22 : 13:42:51

Sorry I didn't post this yesterday, but Thanks soo much! It worked great. I think I learned something also; which is also a good thing. I appreciate your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources