Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0-- Opening Balance for Container
,LPD.Qty-- Quantity Change
,@.ActionType-- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty-- Balance Quantity for Container
,LP.LabelBatchStatus-- Item Status
,NULL-- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah Adarsh
On 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
>+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP