Hi all,
Very new to SQL & have 2 Different questions here please -
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.
Thanks in advance.
Rory
SELECT
E.[Promotions ID],
P.[Promotion Desc.],
P.[Customer Number] AS [Customer Code],
C.[Group Description],
H.[Brand Description] AS [Brand]
FROM View.EventsData AS E
LEFT JOIN View.Promotions AS P
ON E.[Promotion ID] = P.[Promotion ID]
LEFT JOIN View.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'
June 30, 2020 at 11:37 pm
This is really odd:
LEFT JOIN View.Costs AS CO
You're using the keyword VIEW as a schema name? That feels all kinds of No. Don't use reserved words as object names in T-SQL. It may come back to bite you later. Best to avoid that. Does the query run at all? Oh, and you're not using any of the results returned from your OUTER APPLY -- there's no S.[ColumnName] in your SELECT statement.
Any time you type a word in the T-SQL editor and it shows up as any other color than the default color (usually black), it's a reserved word or function in T-SQL, so don't use that as a column name - you're just asking for trouble.
SELECT
E.[Promotions ID],
P.[Promotion Desc.],
P.[Customer Number] AS [Customer Code],
C.[Group Description],
H.[Brand Description] AS [Brand]
FROM View.EventsData AS E
LEFT JOIN View.Promotions AS P
ON E.[Promotion ID] = P.[Promotion ID]
LEFT JOIN View.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';
June 30, 2020 at 11:51 pm
Hi Pietlendin,
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:
SELECT
E.[Promotions ID],
P.[Promotion Desc.],
P.[Customer Number] AS [Customer Code],
C.[Group Description],
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';
July 1, 2020 at 12:19 am
The OUTER APPLY lets you join a table to a table-valued function, which you can't do with LEFT JOIN (that just joins tables/views). The APPLY forces the TOP query to run once for each record in the "outer" query.
OUTER APPLY is quite similar to a LEFT JOIN. The difference between JOIN and APPLY is that when you have A JOIN B, B cannot be a query that refers to A, but you relate B to A with the ON clause. With A APPLY B you don't have any join, but B can be a query (or call to a table-valued function) that refers to A, which is happening here.
To be able to say why the query stalls when you say TOP 1000, we would need to know a lot more. We need to know the query plan, we need to know the view definitions, we need to know the tables and their indexes. All that information may bit a little too much to share here, so you be better off to find someone locally with whom you can discuss the issue.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 2, 2020 at 12:05 am
Thanks Erland!
October 10, 2020 at 11:07 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply