August 6, 2011 at 1:05 am
Hi , when I am trying to execute a query against AdventureWorksDW database in SSMS, the result is just column name but no data in it. It did not throw any error either
Here is the query ..... can some one brief me about cause for it
SELECT FactResellerSales.SalesAmount, DimTime.CalendarYear
FROM DimTime INNER JOIN
FactResellerSales ON DimTime.TimeKey = FactResellerSales.OrderDateKey AND DimTime.TimeKey = FactResellerSales.DueDateKey AND
DimTime.TimeKey = FactResellerSales.ShipDateKey
August 6, 2011 at 4:51 am
You're doing the JOIN incorrectly. See, each of those columns is to be joined to the Time dimension individually. If you look at the data, it will make more sense:
ProductKey OrderDateKeyDueDateKeyShipDateKey
372 762 774 769
That's just the first row from the table. Note that each of the Date keys is different. So your join has it where all the date keys are the same. They're not, anywhere in the data, and that's the problem. So, you need to decided which of the dates you're going for and do something like this:
SELECT frs.SalesAmount,
dd.CalendarYear AS DueDateYear,
od.CalendarYear AS OrderDateYear,
sd.CalendarYear AS ShipDateyear
FROM dbo.FactResellerSales AS frs
JOIN dbo.DimTime AS dd
ON frs.DueDateKey = dd.TimeKey
JOIN dbo.DimTime AS od
ON frs.OrderDateKey = od.TimeKey
JOIN dbo.DimTime AS sd
ON frs.ShipDateKey = sd.TimeKey
Then you'll get resutls
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply