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 2000 Forums
 Transact-SQL (2000)
 Referencing Aliases in Expressions

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 months

SELECT 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 MonthTotal
FROM Stock_Master

shallu1_gupta
Constraint Violating Yak Guru

394 Posts

Posted - 2006-05-25 : 07:34:57
Hi,
you cannot do
(Month1 + Month2) as MonthTotal
Cannot use aliases this way. Can do that in Front End
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-05-25 : 08:21:42
Use derived tables.

select a + b as c
from
(select <some expression> as a, <some expression> as b
from ...
) tmp

Go to Top of Page

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 c
from
(select <some expression> as a, <some expression> as b
from ...
) tmp



Go to Top of Page
   

- Advertisement -