D
d2r2
Hi,
I'm trying to run a nested (UNION) query against a MSAccessXP database
(version 2002; build 10.6501.6714; SP3)
In Access the SQL-statement executes just fine. When I run it in a asp-page
I get the following error:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
Can a nested union-query be used at all or should I use an intermediate
table?
Thanks,
Djurre
Below are the details of the sql I want to use.
The query sums the competition results for several teams.
I have one table with the team-codes. This table is joined with the
match-results for home and away-games.
For home-games the team-code is record in the column game.home-team and for
away-games the team-code is recorded in the column game.away-team.
Per team I want to list: sort-code, team-code, team-name, games played,
wins, draws, losses, points, goals scored, goals against
The sql statement I want to use is like this:
Select
SELECT Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name], sum(played)
AS games, sum(w) AS Wins, sum(D) AS Draws, sum(L) AS Losses, sum(GP) AS
Gamepoints, sum(For) AS GF, sum(Against) AS GA
FROM (
SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 1, 0)) as W,
sum(Iif( WT.[goals-home] = WT.[goals-away] , 1, 0)) as D,
sum(Iif( WT.[goals-home] < WT.[goals-away] , 1, 0)) as L,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WT.[goals-home]) as For,
sum(WT.[goals-away]) as Against
FROM
Teams
left outer JOIN Games as WT ON (Teams.[Team-code] = WT.[Team-code home] and
WT.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
UNION
SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WU.[goals-home] < WU.[goals-away] , 1, 0)) as W,
sum(Iif( WU.[goals-home] = WU.[goals-away] , 1, 0)) as D,
sum(Iif( WU.[goals-home] > WU.[goals-away] , 1, 0)) as L,
sum(Iif( WU[goals-home] < WU.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WU.[goals-away]) as For,
sum(WU.[goals-home]) as Against
FROM
Teams
left outer JOIN Games as WU ON (Teams.[Team-code] = WU.[Team-code away] and
WU.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
)
GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
ORDER BY Teams.[Sort-code];
I'm trying to run a nested (UNION) query against a MSAccessXP database
(version 2002; build 10.6501.6714; SP3)
In Access the SQL-statement executes just fine. When I run it in a asp-page
I get the following error:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
Can a nested union-query be used at all or should I use an intermediate
table?
Thanks,
Djurre
Below are the details of the sql I want to use.
The query sums the competition results for several teams.
I have one table with the team-codes. This table is joined with the
match-results for home and away-games.
For home-games the team-code is record in the column game.home-team and for
away-games the team-code is recorded in the column game.away-team.
Per team I want to list: sort-code, team-code, team-name, games played,
wins, draws, losses, points, goals scored, goals against
The sql statement I want to use is like this:
Select
SELECT Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name], sum(played)
AS games, sum(w) AS Wins, sum(D) AS Draws, sum(L) AS Losses, sum(GP) AS
Gamepoints, sum(For) AS GF, sum(Against) AS GA
FROM (
SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 1, 0)) as W,
sum(Iif( WT.[goals-home] = WT.[goals-away] , 1, 0)) as D,
sum(Iif( WT.[goals-home] < WT.[goals-away] , 1, 0)) as L,
sum(Iif( WT.[goals-home] > WT.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WT.[goals-home]) as For,
sum(WT.[goals-away]) as Against
FROM
Teams
left outer JOIN Games as WT ON (Teams.[Team-code] = WT.[Team-code home] and
WT.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
UNION
SELECT
Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name],
count(*) as Played,
sum(Iif( WU.[goals-home] < WU.[goals-away] , 1, 0)) as W,
sum(Iif( WU.[goals-home] = WU.[goals-away] , 1, 0)) as D,
sum(Iif( WU.[goals-home] > WU.[goals-away] , 1, 0)) as L,
sum(Iif( WU[goals-home] < WU.[goals-away] , 3, Iif(WT.[goals-away] =
WT.[goals-away], 1, 0))) as GP,
sum(WU.[goals-away]) as For,
sum(WU.[goals-home]) as Against
FROM
Teams
left outer JOIN Games as WU ON (Teams.[Team-code] = WU.[Team-code away] and
WU.[Status]="2")
Group by Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
)
GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
ORDER BY Teams.[Sort-code];