[thelist] Things Found while researching other things - Part 2

[Rob Smith]

And my retort tip would be:

<tip type="WITH and SQL 2005" Author="Rob.Smith>
	In SQL 2005, you can use the keyword WITH as an alias:

WITH Sales
AS
(
	Select 
		total_sales, 
		total_margins,
		commission_id
	FROM 
		SalesInvoices
	
	UNION ALL
	
	Select
		total_sales*-1,
		total_margins*-1,
		commission_id
	FROM
		ReturnMemos
)
SELECT 
	sum(S.total_sales) as [Total Sales], 
	sum(S.total_margins) as [Total Margins],
	E.Employee_Name as Name
FROM
	Sales S INNER JOIN Employees E on
	S.commission_id = E.commission_ID

</tip>

Also comes in handy on recursive queries.

 

Rob Smith
LexJet
rob.smith at lexjet.com
http://www.lexjet.com
(800)453-9538
(941)330-1210 Int'l
(941)330-1220 Fax
1680 Fruitville Road, 3rd Floor
Sarasota, FL 34236