Help with these temp tables - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with these temp tables

Author

Topic

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-11 : 12:32:05

In these two tables im just to bring the data back where the two DesignID's dont match. Im gettin an error

Server: Msg 107, Level 16, State 3, Line 1
The column prefix '#ttTopSellers' does not match with a table name or alias name used in the query.

Declare @CustomerID as VARCHAR(25)
Set @CustomerID = 'DELCOZ01-10'

/*Figure the designs that stores carry*/
Select Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID] as RegionID, Region.Name
Into #ttDesign
From Mas.dbo.Item Item
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Inner Join Mas.dbo.DesignRegionIndex DRI
on Design.[ID] = DRI.DesignID
Inner Join MAS.dbo.Region Region
on DRI.RegionID = Region.[ID]
Inner Join MAS.dbo.CustomerClassificationRegionIndex CRI
on Region.[ID] = CRI.RegionID
Inner Join MAS.dbo.CustomerClassification CC
on CRI.CustomerClassificationID = CC.[ID]

Where @CustomerID = CustomerID
Group By Design.Description, Item.DesignID,
CustomerClassificationID, CustomerID, Region.[ID], Region.Name

/*This finds the top retail sales globally*/
Select Top 10 Sum(Sales) as Sales, DesignID, Design.[Description]
Into #ttTopSellers
From Reporting.dbo.RetailSales_ByStore_ByCustomer_ByDay_ByItem DI
Inner Join Mas.dbo.Item Item
on DI.ItemNumber = Item.ItemNumber
Inner Join MAS.dbo.Style Style
on Item.StyleID = Style.[ID]
Inner Join MAS.dbo.Line Line
on Style.LineID = Line.[ID]
Inner Join MAS.dbo.Design Design
on Item.DesignID = Design.[ID]
Where [Date] >= Month(getdate())-12
and DesignID <> 0
Group By DesignID, Design.[Description]
Order by Sum(Sales) Desc

Select *
From #ttDesign
Where #ttDesign.DesignID <> #ttTopSellers.DesignID

--Drop Table #ttDesign
--Drop Table #ttTopSellers

sshelper
Posting Yak Master

216 Posts

Posted - 2007-12-11 : 12:53:49

Try changing this part:

Select *
From #ttDesign
Where #ttDesign.DesignID <> #ttTopSellers.DesignID

to this:

Select *
From #ttDesign LEFT OUTER JOIN #ttTopSellers
ON #ttDesign.DesignID = #ttTopSellers.DesignID
Where #ttTopSellers.DesignID IS NULL

SQL Server Helper
http://www.sql-server-helper.com

gavakie
Posting Yak Master

221 Posts

Posted - 2007-12-11 : 13:05:38

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources