Nested MSAccess Union-Queries in asp

[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];

[Bob Barrows [MVP]]

I'm surprised it runs in Access without an alias for the subquery. The basic
syntax should be:

Select q.col1,...,q.colN FROM (select ...) As q

Personally, I would put the whole thing into a saved query and execute the
saved query from asp:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery rs

ADO allows you to run a stored procedure/saved query by treating it as if it
was a builtin method of the Connection object. If the saved query accepts
parameters, then you simply supply them as if they were arguments for the
method:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery parm1,...,parmN, rs

And if the query does not return records, simply leave off the recordset
variable:
conn.NameOfSavedQuery parm1,...,parmN

See here for a little more:
http://www.google.com/groups?selm=eE...&output=gplain -

I just noticed something else. See below:

d2r2 wrote:

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?
<snip> Select
SELECT
This also seems suspicious. Two SELECT keywords in a row???
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 ( <snip> )
See? No alias. The query engine does not know what is meant by "Teams." in
your SELECT, GROUP BY and WHERE clauses. You need to put

As Teams

here.
GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
ORDER BY Teams.[Sort-code];

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[d2r2]

Bob,
thanks for your pointers.
I noticed the typo (select select) too. I began to type in the statement and
then pasted it in anyway.

Initially I tried using a saved query but that didn't work as I connect thru
a DSN on the hosting service supplier webserver I use.
Locally the saved query does work thru IIS/Microsoft.Jet.OLEDB.4.0.

Found the bug.
In the asp-version of the sql-statement I included a "+" too many. Removed
that , and now it works just fine.

thanks again,
Djurre
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...

I'm surprised it runs in Access without an alias for the subquery. The
basic syntax should be:

Select q.col1,...,q.colN FROM (select ...) As q

Personally, I would put the whole thing into a saved query and execute the
saved query from asp:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery rs

ADO allows you to run a stored procedure/saved query by treating it as if
it was a builtin method of the Connection object. If the saved query
accepts parameters, then you simply supply them as if they were arguments
for the method:

set rs=createobject("adodb.recordset")
conn.NameOfSavedQuery parm1,...,parmN, rs

And if the query does not return records, simply leave off the recordset
variable:
conn.NameOfSavedQuery parm1,...,parmN

See here for a little more:
http://www.google.com/groups?selm=eE...&output=gplain -

I just noticed something else. See below:

d2r2 wrote:

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?

<snip>

Select
SELECT

This also seems suspicious. Two SELECT keywords in a row???

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 (

<snip>

)

See? No alias. The query engine does not know what is meant by "Teams." in
your SELECT, GROUP BY and WHERE clauses. You need to put

As Teams

here.

GROUP BY Teams.[Sort-code], Teams.[Team-code], Teams.[Team-name]
ORDER BY Teams.[Sort-code];

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"