Nested MSAccess Union-Queries in asp

Discussion in 'ASP General' started by d2r2, Dec 28, 2004.

  1. d2r2

    d2r2 Guest

    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];
     
    d2r2, Dec 28, 2004
    #1
    1. Advertising

  2. 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=&oe=UTF-8&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"
     
    Bob Barrows [MVP], Dec 28, 2004
    #2
    1. Advertising

  3. d2r2

    d2r2 Guest

    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]" <> wrote in message
    news:%...
    > 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=&oe=UTF-8&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, Dec 28, 2004
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Siegfried Heintze
    Replies:
    1
    Views:
    778
    Paul Henderson
    Apr 15, 2006
  2. Matt Garman
    Replies:
    1
    Views:
    672
    Matt Garman
    Apr 25, 2004
  3. Ian Roddis

    xslt queries in xml to SQL queries

    Ian Roddis, Feb 26, 2006, in forum: Python
    Replies:
    3
    Views:
    1,521
    Crutcher
    Feb 26, 2006
  4. Tagore

    structure and union queries

    Tagore, Apr 7, 2009, in forum: C Programming
    Replies:
    37
    Views:
    1,105
    Ben Bacarisse
    Apr 13, 2009
  5. Abby Lee

    so many queries within queries I'm confused

    Abby Lee, Aug 4, 2004, in forum: ASP General
    Replies:
    11
    Views:
    362
    Aaron [SQL Server MVP]
    Aug 6, 2004
Loading...

Share This Page