Thanks for your prompt response.
Yes you are correct I am NOT using View as a Schema name. It was just to give the audience a brief idea that the data is coming from a View.
Refined Query below : It runs fine. Also, S.Column Name is now added in the query.
Can you please have a look again and advise what is OUTER APPLY doing here?
And do you have any suggestion for the below?
2. Also, what can be a reason of me not being able to select TOP 1000* rows from a view? I even tried running TOP 10 rows it just goes on forever and doesn't return anything. I am on a prod server so doesn't want to cause any issues. But is there any other query I could try and return me some data and we can take it further from there.
Refined query below:
P.[Customer Number] AS [Customer Code],
H.[Brand Description] AS [Brand],
S.[Promo Price] AS [Scan Price]
FROM Live.EventsData AS E
LEFT JOIN Live.Promotions AS P
ON E.[Promotion ID] = P.[Promotion ID]
LEFT JOIN Live.Costs AS CO
ON E.[Product Code] = CO.[Product Code]
OUTER APPLY (
SELECT TOP 1 *
FROM Fact.PromotionData AS S
WHERE E.[Promotions ID] = S.[Promotion]
AND E.[Product Code] = S.[Product Code]
) AS S
LEFT JOIN Dim.ProductHierarchy AS H
ON E.[Product Code] = H.[Product Code]
LEFT JOIN Dim.CustomerHierarchy AS C
ON P.[Customer Number] = C.[Customer Number]
LEFT JOIN Dim.Time as T
ON P.[Start Date] = T.[Date]
WHERE C.[Group Description] IN ('Supermarket')
AND P.[Start Date] > '1 January 2020';