Replace View with Join or SubQuery

My application uses a View stored in a database. I have queries that join
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>

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

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