carl Brooks - 1 year ago 48
SQL Question

# Converting Oracle code to SQL Server for a fixture list

I have been kindly given an oracle solution to a particular problem but been struggling to convert it to SQL Server code. I get no errors but get no results. Below is my version using SQL Server and I have provided a link to the question, so you can see the answer I am referring to (the answer is the one marked as best answer).

Here is the link: How to code a certain maths algorithm

Attempted SQL code:

``````WITH League_Teams (id, leagueid, idx, is_fake, num_teams, num_fake) AS (
-- Generate a unique-per-league index for each team that is between 0
-- and the (number of teams - 1) and calculate the number of teams
-- and if this is an odd number then generate a fake team as well.
SELECT TeamID,
LeagueID,
ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1,
0,
COUNT(1) OVER ( PARTITION BY LeagueID ),
(COUNT(1) OVER ( PARTITION BY LeagueID ) % 2)
FROM Team
UNION ALL
SELECT NULL,
LeagueID,
COUNT(1),
1,
COUNT(1),
1
FROM   Team
GROUP BY LeagueID
HAVING COUNT(1) % 2  > 0
),
cte ( home_idx, away_idx, week_number, leagueID, num_teams, num_fake ) AS (
-- Calculate round 1 games
SELECT idx,
num_teams + num_fake - 1 - idx,
1,
LeagueID,
num_teams,
num_fake
FROM   league_teams
WHERE  2 * idx < num_teams
UNION ALL
--  Generate successive rounds with the two cases when the away team has the maximum index or otherwise.
SELECT CASE away_idx
WHEN num_teams + num_fake - 1
THEN home_idx + 1
ELSE home_idx + 1 % num_teams + num_fake -1
END,
CASE away_idx
WHEN num_teams + num_fake - 1
THEN away_idx
ELSE away_idx + 1 % num_teams + num_fake - 1
END,
week_number + 1,
LeagueID,
num_teams,
num_fake
FROM  cte
WHERE week_number < num_teams + num_fake - 1
)
INSERT INTO dbo.Fixture
-- Join the cte results back to the League_Teams table to convert
-- Indexes used in calculation back to the actual team ids.
SELECT rn,
week_number,
NULL,
h.id,
a.id,
c.leagueid
FROM   (
--Keeps the results in a nice order.
SELECT ROW_NUMBER() OVER (ORDER BY LeagueID)  AS rn,
t.*
FROM   (
-- Duplicate the results swapping home and away.
SELECT week_number,
home_idx,
away_idx,
LeagueId
FROM   cte
UNION ALL
SELECT week_number + num_teams + num_fake - 1,
away_idx,
home_idx,
LeagueId
FROM   cte
) t
) c
INNER JOIN League_Teams h
ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )
INNER JOIN League_Teams a
ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )
ORDER BY rn;
``````

Below code might help.

Removed column list from CTE declaration and used column aliases inside CTE.

``````WITH League_Teams AS (
-- Generate a unique-per-league index for each team that is between 0
-- and the (number of teams - 1) and calculate the number of teams
-- and if this is an odd number then generate a fake team as well.
SELECT TeamID AS id,
LeagueID AS leagueid,
ROW_NUMBER() OVER ( PARTITION BY LeagueID ORDER BY TeamID ) - 1 AS idx,
0 AS is_fake,
COUNT(1) OVER ( PARTITION BY LeagueID ) AS num_teams,
(COUNT(1) OVER ( PARTITION BY LeagueID ) % 2) AS num_fake
FROM Team
UNION ALL
SELECT NULL,
LeagueID,
COUNT(1),
1,
COUNT(1),
1
FROM   Team
GROUP BY LeagueID
HAVING COUNT(1) % 2  > 0
),
cte AS (
-- Calculate round 1 games
SELECT idx AS home_idx,
num_teams + num_fake - 1 - idx AS away_idx,
1 AS week_number,
LeagueID AS leagueID,
num_teams,
num_fake
FROM   league_teams
WHERE  2 * idx < num_teams
UNION ALL
--  Generate successive rounds with the two cases when the away team has the maximum index or otherwise.
SELECT CASE away_idx
WHEN num_teams + num_fake - 1
THEN home_idx + 1
ELSE home_idx + 1 % num_teams + num_fake -1
END,
CASE away_idx
WHEN num_teams + num_fake - 1
THEN away_idx
ELSE away_idx + 1 % num_teams + num_fake - 1
END,
week_number + 1,
LeagueID,
num_teams,
num_fake
FROM  cte
WHERE week_number < num_teams + num_fake - 1
)
INSERT INTO dbo.Fixture
-- Join the cte results back to the League_Teams table to convert
-- Indexes used in calculation back to the actual team ids.
SELECT rn,
week_number,
NULL,
h.id,
a.id,
c.leagueid
FROM   (
--Keeps the results in a nice order.
SELECT ROW_NUMBER() OVER (ORDER BY LeagueID)  AS rn,
t.*
FROM   (
-- Duplicate the results swapping home and away.
SELECT week_number,
home_idx,
away_idx,
LeagueId
FROM   cte
UNION ALL
SELECT week_number + num_teams + num_fake - 1,
away_idx,
home_idx,
LeagueId
FROM   cte
) t
) c
INNER JOIN League_Teams h
ON ( c.home_idx = h.idx AND c.leagueId = h.leagueID )
INNER JOIN League_Teams a
ON ( c.away_idx = a.idx AND c.leagueId = a.leagueID )
ORDER BY rn;
``````