Using MSDE...(8.00.760 SP3)
I have the following query...I taking this from ms-access, but lets just
stick to using the EM here...
SELECT ProductName, SerialNumber, UnitPrice,
(SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID) AS
TotalAddStock,
(SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID) AS
TotalSoldStock
FROM dbo.Products
The problem is that I want to use the alias fields in a expression, and add
one more column to the above like:
(TotalAddStock - TotalSoldStock) as InStock.
In JET sql, I can simple use the above expression, and add it to the above
sql (ie: using those alias fields in expressions in the sql is legal).
However, In the EM, when I try to add the above expression...it says that
TotalAddStock is a not a valid field.
Is there some rule for using Alias fields?
Do I have to repeat the actual sql for those two sub-queries all over again
to make the expression of
( ("sub query repeated again") - ("sub query
repeated again") ) as InStock.
I have noticed that if I save the query (create a view),then I can certainly
write the following:
select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from myCoolView
However, this means I now have two sql views in place of what was one
before. It would be handy if I could throw in that one extra column.
Is creating two views here the recommend approach here? Any enlightenment on
using Alias fields in other column expressions is appreciated.
My best guess seems that you can't use a sub-query as a alias field in other
expressions...is this right?
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msnAlbert,
Try nesting the statement this way:
select
ProductName,
SerialNumber,
UnitPrice,
TotalAddStock,
TotalSoldStock,
(TotalAddStock - TotalSoldStock) as InStock
from (
SELECT
ProductName,
SerialNumber,
UnitPrice,
(
SELECT SUM(UnitsAdd)
FROM inventory
WHERE ProductID = Products.ProductID
) AS TotalAddStock,
(
SELECT SUM(Quantity)
FROM OrderDetails
WHERE ProductID = Products.ProductID
) AS TotalSoldStock
FROM dbo.Products
) T
This accomplishes what you discovered worked with a view - here you
would be using a derived table, which is basically an ad-hoc view.
Steve Kass
Drew University
Albert D. Kallal wrote:
>Using MSDE...(8.00.760 SP3)
>I have the following query...I taking this from ms-access, but lets just
>stick to using the EM here...
>SELECT ProductName, SerialNumber, UnitPrice,
> (SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID) AS
>TotalAddStock,
> (SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID) AS
>TotalSoldStock
>FROM dbo.Products
>The problem is that I want to use the alias fields in a expression, and add
>one more column to the above like:
> (TotalAddStock - TotalSoldStock) as InStock.
>In JET sql, I can simple use the above expression, and add it to the above
>sql (ie: using those alias fields in expressions in the sql is legal).
>However, In the EM, when I try to add the above expression...it says that
>TotalAddStock is a not a valid field.
>Is there some rule for using Alias fields?
>Do I have to repeat the actual sql for those two sub-queries all over again
>to make the expression of
> ( ("sub query repeated again") - ("sub query
>repeated again") ) as InStock.
>I have noticed that if I save the query (create a view),then I can certainly
>write the following:
>select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from myCoolView
>However, this means I now have two sql views in place of what was one
>before. It would be handy if I could throw in that one extra column.
>Is creating two views here the recommend approach here? Any enlightenment on
>using Alias fields in other column expressions is appreciated.
>My best guess seems that you can't use a sub-query as a alias field in other
>expressions...is this right?
>
>|||Both of these will, in all likelihood, force correlated subqueries--highly
inefficient. I would check the execution plans genereated to make sure.
Here's an alternative:
SELECT p.ProductName
,p.SerialNumber
,p.UnitPrice
,tas.TotalAddStock
,tss.TotalSoldStock
,InStock = (tas.TotalAddStock - tss.TotalSoldStock)
FROM dbo.Products AS p
INNER JOIN
(SELECT ProductID
,TotalAddStock = SUM(UnitsAdd)
FROM dbo.Inventory
GROUP BY ProductID
) AS tas
ON p.ProductID = tas.ProductID
(SELECT ProductID
,TotalSoldStock = SUM(Quantity)
FROM dbo.OrderDetails
GROUP BY ProductID
) AS tss
ON p.ProductID = tss.ProductID
Now, this too could be expensive in that the Inventory and/or OrderDetails
tables could be very large and creating the aggregates on all Products before
the JOIN restrictions could be CPU wasteful.
However, in this case, it looks like you already have an implicit RI
relationship between the Products, Inventory, and Order Details tables and,
with no restrictions on the final results, would have to calculate all those
aggregates anyway.
So, in this case, as long as the Optimizer would have choosen Lazy Spooling
for the first two examples, the solution provided here, a Set-Based example,
would be orders of magnitude faster.
Sincerely,
Anthony Thomas
"Steve Kass" wrote:
> Albert,
> Try nesting the statement this way:
> select
> ProductName,
> SerialNumber,
> UnitPrice,
> TotalAddStock,
> TotalSoldStock,
> (TotalAddStock - TotalSoldStock) as InStock
> from (
> SELECT
> ProductName,
> SerialNumber,
> UnitPrice,
> (
> SELECT SUM(UnitsAdd)
> FROM inventory
> WHERE ProductID = Products.ProductID
> ) AS TotalAddStock,
> (
> SELECT SUM(Quantity)
> FROM OrderDetails
> WHERE ProductID = Products.ProductID
> ) AS TotalSoldStock
> FROM dbo.Products
> ) T
> This accomplishes what you discovered worked with a view - here you
> would be using a derived table, which is basically an ad-hoc view.
> Steve Kass
> Drew University
> Albert D. Kallal wrote:
> >Using MSDE...(8.00.760 SP3)
> >
> >I have the following query...I taking this from ms-access, but lets just
> >stick to using the EM here...
> >
> >SELECT ProductName, SerialNumber, UnitPrice,
> > (SELECT SUM(UnitsAdd)
> > FROM inventory
> > WHERE ProductID = Products.ProductID) AS
> >TotalAddStock,
> > (SELECT SUM(Quantity)
> > FROM OrderDetails
> > WHERE ProductID = Products.ProductID) AS
> >TotalSoldStock
> >FROM dbo.Products
> >
> >The problem is that I want to use the alias fields in a expression, and add
> >one more column to the above like:
> >
> > (TotalAddStock - TotalSoldStock) as InStock.
> >
> >In JET sql, I can simple use the above expression, and add it to the above
> >sql (ie: using those alias fields in expressions in the sql is legal).
> >
> >However, In the EM, when I try to add the above expression...it says that
> >TotalAddStock is a not a valid field.
> >
> >Is there some rule for using Alias fields?
> >
> >Do I have to repeat the actual sql for those two sub-queries all over again
> >to make the expression of
> >
> > ( ("sub query repeated again") - ("sub query
> >repeated again") ) as InStock.
> >
> >I have noticed that if I save the query (create a view),then I can certainly
> >write the following:
> >
> >select productName, SerialNumber, UnitPrice, TotaAddStock,TotalSoldStock,
> > (TotalAddStock - TotalSoldStock) as InStock
> > from myCoolView
> >
> >However, this means I now have two sql views in place of what was one
> >before. It would be handy if I could throw in that one extra column.
> >
> >Is creating two views here the recommend approach here? Any enlightenment on
> >using Alias fields in other column expressions is appreciated.
> >
> >My best guess seems that you can't use a sub-query as a alias field in other
> >expressions...is this right?
> >
> >
> >
>|||Interesting...but it seems that the query is optimized quite well. (I just
threw it into the Query analyzer).
It runs is less then one second for 125,000 invoice details and 2500
inventory records (stock additions), and 500 products.
Interesting, it also runs Steve's example in the same time..and thus the
query optimizer seems quite smart in this case. (that to me is rather
amazing...since I do have reasonable grasp of sql...and I can't believe
that sql server returns such good times for the kind of problems that don't
look at all that you can resolve those values together).
As for the joins,
I did have to put in a "left" join..since you were joining more then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
Anyway, I am surprised the engine does such a good job, considering you are
using a "group by" here..and that is joined to the "main" table. Often, jet
don't do such a good job in these cases. I should note that using the
sub-quires like I did does run very well in JET, and also sql server.
Anyway...thanks Anthony and Steve...
I am now temped to simply use a view here as per my original idea (I can
more easily "think" of my original syntax). However, I do "get" what you did
with the "group by"...and the fact that sql server seems to be more
forgiving for complex sql then is JET.
I certainly appreciate your answers, and in fact the two answers being so
different solutions also opens my mind up here...
Great stuff guys...and thanks!
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help but you made a comment I'd like to follow up with, if I may.
You said, "I did have to put in a "left" join..since you were joining more
then one
table here. So, I just changed the joins to:
LEFT OUTER JOIN
"
The number of tables joined has nothing to do with the join types. They
were coded as INNER JOINS so that you'd only get matching records; that is,
records with ProductID's that existed in each of the joined tables. If you
use LEFT JOINs, you will get Products that do not have corresponding
Inventory or OrderDetail records, but then the values your are computing,
TotalAdd and TotalSold will be NULL for those record types.
The INNER JOINS will use the Primary Keys defined on the child and parent
tables when performing the join operation and will likely use a Cluster Index
Scan if you use the LEFT JOIN systax. Thus, it is more likely that the INNER
JOIN will give you better performance, especially if you want only the
records that will actually compute a value instead returning NULL results.
Also, I'm curious what the execution plan and run time were if you run the
snippet as I originally wrote it. I'm curious if the Optimizer was able to
resolve to the same execution plan. For this to be meaningful, you'll have
to use the multiple INNER JOINs because that was what the two original
queries where doing, in essence.
Thanks for helping me satisfy my curiosity.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Interesting...but it seems that the query is optimized quite well. (I just
> threw it into the Query analyzer).
> It runs is less then one second for 125,000 invoice details and 2500
> inventory records (stock additions), and 500 products.
> Interesting, it also runs Steve's example in the same time..and thus the
> query optimizer seems quite smart in this case. (that to me is rather
> amazing...since I do have reasonable grasp of sql...and I can't believe
> that sql server returns such good times for the kind of problems that don't
> look at all that you can resolve those values together).
> As for the joins,
> I did have to put in a "left" join..since you were joining more then one
> table here. So, I just changed the joins to:
> LEFT OUTER JOIN
> Anyway, I am surprised the engine does such a good job, considering you are
> using a "group by" here..and that is joined to the "main" table. Often, jet
> don't do such a good job in these cases. I should note that using the
> sub-quires like I did does run very well in JET, and also sql server.
> Anyway...thanks Anthony and Steve...
> I am now temped to simply use a view here as per my original idea (I can
> more easily "think" of my original syntax). However, I do "get" what you did
> with the "group by"...and the fact that sql server seems to be more
> forgiving for complex sql then is JET.
> I certainly appreciate your answers, and in fact the two answers being so
> different solutions also opens my mind up here...
> Great stuff guys...and thanks!
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>|||Ah,...yes...inner join does work!
(and...yes...some products might not have any invoice sold...so, likely I
will stick with left joins).
Anyway..here is the query plans for both:
http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@.msn.com
http://www.attcanada.net/~kallal.msn|||Glad I could help. Thanks for a peek at the EEP. You'll notice that the
Optimizer picked the same execution steps just changed the order of the joins.
It is concerning that that you are running Table Scans. This happens
because we are not limiting the results but aggregating all records. The
point is, you will have to touch every record. The fact you are doing Table
Scans indicates you have not defined a Clustered Index for any of these three
tables.
Every Table should have a Primary Key defined, for integrity and you are not
even in 1NF if you haven't defined a key, which means you do not have a
relational table. Bad mojo. Second, every Table should have a Clustered
Index defined. The reason being, this defines the actual data sort for
storage. Usually, the Primary Key and the Clustered Index are NOT the same,
at least with how many use them.
Sincerely,
Anthony Thomas
"Albert D. Kallal" wrote:
> Ah,...yes...inner join does work!
> (and...yes...some products might not have any invoice sold...so, likely I
> will stick with left joins).
> Anyway..here is the query plans for both:
> http://www.attcanada.net/~kallal.msn/sqlplan/index.htm
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@.msn.com
> http://www.attcanada.net/~kallal.msn
>
>
