March 12, 2015 at 12:32 pm
I am obviously missing something here because making this run as dynamic sql seems a layer of complexity that just isn't needed here. It sort of looks like a dynamic pivot but all the values are hard coded so the dynamic ability of the pivot isn't there. Either that or I need more coffee.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 12:32 pm
Same errors.
March 12, 2015 at 12:36 pm
The values are all hard-coded because that is the only way I can it to run for now. The point of the PIVOTS are to eventually replace the hard-coded values with the variable, which I can't get to work. I do have one line with the values replaced by the variable so it can be seen what I'm trying to do.
March 12, 2015 at 12:41 pm
measterbro (3/12/2015)
The values are all hard-coded because that is the only way I can it to run for now. The point of the PIVOTS are to eventually replace the hard-coded values with the variable, which I can't get to work. I do have one line with the values replaced by the variable so it can be seen what I'm trying to do.
Gotcha. You are trying to turn a static pivot into a dynamic pivot. I have a meeting shortly but I will touch base either later tonight or tomorrow and see if any progress has been made.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2015 at 12:42 pm
Exactly. Thank you.
March 12, 2015 at 3:22 pm
I'm sure your code can get many improvements. Could you post some sample data and DDL to be able to test?
March 12, 2015 at 5:25 pm
Unfortunately I am not able to post data or DDL since it is proprietary. I just thought there might be a known problem with dynamic SQL and appending variables, such as needing to break up the SQL into smaller chunks.
March 13, 2015 at 3:55 am
measterbro (3/12/2015)
Unfortunately I am not able to post data or DDL since it is proprietary. I just thought there might be a known problem with dynamic SQL and appending variables, such as needing to break up the SQL into smaller chunks.
No, but as has already been pointed out, the code is unnecessarily longwinded which won't help.
There are some significant code smells in the GamesWon CTE which warrant further investigation because resolution would reduce the size and complexity of the statement you are dealing with, perhaps as much as 6-fold – and improve performance by a similar factor.
There’s repeated use if two very similar subqueries as follows (note the use of table aliases):
SELECT d.Division_ID
FROM Divisions d
INNER JOIN Conferences c
ON d.Conference_ID = c.Conference_ID
INNER JOIN Leagues l
ON c.League_ID = l.League_ID
WHERE l.League_ID = @league
AND c.Conference_ID = @conf1
SELECT d.Division_ID
FROM Divisions d
INNER JOIN Conferences c
ON d.Conference_ID = c.Conference_ID
INNER JOIN Leagues l
ON c.League_ID = l.League_ID
WHERE l.League_ID = @league
AND c.Conference_ID = @conf2
The joins in these subqueries are different to those in the FROM clause:
FROM Games g
LEFT OUTER JOIN Conferences c
ON g.HConf = c.Conference_ID
LEFT OUTER JOIN Divisions d
ON g.ADiv = d.Division_ID
LEFT OUTER JOIN Leagues l
ON c.League_ID = l.League_ID
Are any columns from these left-joined tables referenced anywhere in the query?
What happens if you comment them out?
FROM Games g
--LEFT OUTER JOIN Conferences c
--ON g.HConf = c.Conference_ID
--LEFT OUTER JOIN Divisions d
--ON g.ADiv = d.Division_ID
--LEFT OUTER JOIN Leagues l
--ON c.League_ID = l.League_ID
I can’t see any reason why GamesWon should reference any of these tables more than once. Something like this:
SELECT
g.HomeTeam AS Team,
LeagueName, -- which table is this from?
ShortName, -- which table is this from?
Division_Name, -- which table is this from?
(g.NumYear / @TimePeriod * @TimePeriod) AS Season, --??
CASE WHEN [H_SC] > [A_SC] AND d.Conference_ID = @conf1 THEN 1 ELSE 0 END AS ALwins,
CASE WHEN [H_SC] < [A_SC] AND d.Conference_ID = @conf1 THEN 1 ELSE 0 END AS ALlosses,
CASE WHEN [H_SC] = [A_SC] AND d.Conference_ID = @conf1 THEN 1 ELSE 0 END AS ALties,
CASE WHEN [H_SC] > [A_SC] AND d.Conference_ID = @conf2 THEN 1 ELSE 0 END AS NLwins,
CASE WHEN [H_SC] < [A_SC] AND d.Conference_ID = @conf2 THEN 1 ELSE 0 END AS NLlosses,
CASE WHEN [H_SC] = [A_SC] AND d.Conference_ID = @conf2 THEN 1 ELSE 0 END AS NLties
FROM Games g
LEFT JOIN (
SELECT d.Division_ID, d.Conference_ID
FROM Divisions d
INNER JOIN Conferences c
ON d.Conference_ID = c.Conference_ID
INNER JOIN Leagues l
ON c.League_ID = l.League_ID
WHERE l.League_ID = @league
AND c.Conference_ID IN (@conf1, @conf2)
) d
ON d.Division_ID = g.ADiv
WHERE g.HDiv = @div
AND g.NumYear BETWEEN @season AND @season + @TimePeriod - 1
AND g.RS LIKE @rs
AND Neutral LIKE @Neutral
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2015 at 6:59 am
Your GamesWon definition gives these errors:
Msg 207, Level 16, State 1, Line 15
Invalid column name 'LeagueName'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'ShortName'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'Division_Name'.
The columns come from:
'LeagueName' from Leagues
'ShortName' from Conferences
'Division_Name' from Divisions
The definition in my original code is repeated because it must return win, loss, and tie data for both home and away games, and for 2 different conferences (Conferences.Conference_ID = @conf1 and Conferences.Conference_ID = @conf2). Thus the 4 iterations.
The "[ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)" is there so the divisions are not hard-coded.
The "FROM" statements use different criteria:
FROM Games LEFT OUTER JOIN
Conferences ON Games.HConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.ADiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
(H.Conf and Adiv)
FROM Games LEFT OUTER JOIN
Conferences ON Games.AConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.HDiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
(Aconf and HDiv)
March 13, 2015 at 7:43 am
measterbro (3/13/2015)
Your GamesWon definition gives these errors:Msg 207, Level 16, State 1, Line 15
Invalid column name 'LeagueName'.
Msg 207, Level 16, State 1, Line 16
Invalid column name 'ShortName'.
Msg 207, Level 16, State 1, Line 17
Invalid column name 'Division_Name'.
Not bad given the absence of sample data to test against.
The columns come from:
'LeagueName' from Leagues
'ShortName' from Conferences
'Division_Name' from Divisions
The definition in my original code is repeated because it must return win, loss, and tie data for both home and away games, and for 2 different conferences (Conferences.Conference_ID = @conf1 and Conferences.Conference_ID = @conf2). Thus the 4 iterations.
Given a little sample data, the good folks here could show you how to turn this strange beast into a logical, trim, fast query reading each table only once.
The "[ADiv] IN (SELECT Divisions.Division_ID
FROM Divisions INNER JOIN
Conferences ON Divisions.Conference_ID = Conferences.Conference_ID INNER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
WHERE (Leagues.League_ID = @league) AND (Conferences.Conference_ID = @conf1)" is there so the divisions are not hard-coded.
The "FROM" statements use different criteria:
FROM Games LEFT OUTER JOIN
Conferences ON Games.HConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.ADiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
(H.Conf and Adiv)
FROM Games LEFT OUTER JOIN
Conferences ON Games.AConf = Conferences.Conference_ID LEFT OUTER JOIN
Divisions ON Games.HDiv = Divisions.Division_ID LEFT OUTER JOIN
Leagues ON Conferences.League_ID = Leagues.League_ID
(Aconf and HDiv)
Come on, you know you want to. In return you get a tried and tested solution to this problem and a first-rate training session to boot. What's not to like? 😀
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2015 at 7:46 am
i split my query into ~4K pieces and assigned each piece to a variable. Then i assigned them to one, and executed it.
SET @DynamicPivotQuery = @part1 + @part2 + @part3 + @part4;
EXEC sp_executesql @DynamicPivotQuery
It now works correctly.
March 13, 2015 at 7:46 am
measterbro (3/12/2015)
Unfortunately I am not able to post data or DDL since it is proprietary. I just thought there might be a known problem with dynamic SQL and appending variables, such as needing to break up the SQL into smaller chunks.
There won't be much proprietary data in sports league results. What little there is, you could obfuscate. You've already posted ddl for the games table.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 13, 2015 at 7:54 am
measterbro (3/13/2015)
i split my query into ~4K pieces and assigned each piece to a variable. Then i assigned them to one, and executed it.SET @DynamicPivotQuery = @part1 + @part2 + @part3 + @part4;
EXEC sp_executesql @DynamicPivotQuery
It now works correctly.
No it doesn't! It's a huge lumbering overblown monster hogging ten times the resources and taking ten times as long to run as it should. You're only one third [/url]of the way there:
Make it work - it returns correct results
Make it fast - not started
Make it pretty - not started
Surely you don't want to leave the job unfinished?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 18, 2015 at 3:14 am
I've aware that when you concatenate strings, the data type of the entire concatenated string will be set according to the implicit data type of the first item, which can cause the kind of problem you had. So if the first item is an ordinary string, the whole concatenated string will be chopped down to the maximum length of 4000 for unicode or 8000 for non-unicode. Maybe making the first item an explicitly NVARCHAR(MAX) data type will help, like this:
SET @DynamicPivotQuery = CONVERT(NVARCHAR(MAX),N'') +N'.....your concatenated stuff here...
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply