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.
Author |
Topic |
RichardF
Starting Member
2 Posts |
Posted - 2006-05-25 : 07:27:56
|
I have recently converted over from Sybase to SQL Server 2005 & am struggling with Aliases.In my Select statement I have a number of date-based subqueries, the results of which I wish to add together. SQL Server seems to be objecting to me adding Aliases together as it doesn't recognise them as columns.I know I can achieve this by writing it all out in long-hand, but once I start checking for Nulls this is going to get immense.Is there an easy way to do this in SQL Server ?? - I really don't want to create a View either as I want to be able to hand this over to Users.Example below - real code will include 5 monthsSELECT StockCode, Description, (SELECT SUM(Sales_Document_Line.Amount) AS Expr1 FROM Sales_Document_Line INNER JOIN Sales_Document_Header ON Sales_Document_Line.Company = Sales_Document_Header.Company AND Sales_Document_Line.DocumentNo = Sales_Document_Header.DocumentNo WHERE (MONTH(Sales_Document_Header.PostingTaxPointDate) = 1) AND (YEAR(Sales_Document_Header.PostingTaxPointDate) = 2006) AND (Sales_Document_Line.StockCode = Stock_Master.StockCode)) AS Month1, (SELECT SUM(Sales_Document_Line_4.Amount) AS Expr1 FROM Sales_Document_Line AS Sales_Document_Line_4 INNER JOIN Sales_Document_Header AS Sales_Document_Header_4 ON Sales_Document_Line_4.Company = Sales_Document_Header_4.Company AND Sales_Document_Line_4.DocumentNo = Sales_Document_Header_4.DocumentNo WHERE (MONTH(Sales_Document_Header_4.PostingTaxPointDate) = 2) AND (YEAR(Sales_Document_Header_4.PostingTaxPointDate) = 2006) AND (Sales_Document_Line_4.StockCode = Stock_Master.StockCode)) AS Month2, (Month1 + Month2) as MonthTotalFROM Stock_Master |
|
shallu1_gupta
Constraint Violating Yak Guru
394 Posts |
Posted - 2006-05-25 : 07:34:57
|
Hi, you cannot do (Month1 + Month2) as MonthTotalCannot use aliases this way. Can do that in Front End |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-05-25 : 08:21:42
|
Use derived tables. select a + b as cfrom (select <some expression> as a, <some expression> as b from ... ) tmp |
|
|
RichardF
Starting Member
2 Posts |
Posted - 2006-05-25 : 08:25:28
|
Thankyou Dr Cross.That will do nicely.quote: Originally posted by jsmith8858 Use derived tables. select a + b as cfrom (select <some expression> as a, <some expression> as b from ... ) tmp
|
|
|
|
|
|