×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Database Results Error

Database Results Error

Database Results Error

(OP)
I've got a Group By query set up as a view in MySQL.  The query/view works fine when run from MySQL, but gives the following error when incorporated as a result set in a FrontPage web page I've published:
"Database Results Error
Description: Multiple-step operation generated errors. Check each status value.
Number: -2147217887 (0x80040E21)
Source: Microsoft Cursor Engine"

The query looks like:

#SELECT p.plast as 'Player Last', p.pfirst as 'Player #First', DATE_FORMAT(e.date, '%c-%e-%Y') as 'Date',
#date_format(e.begin, '%l:%i %p') as 'Start', o.Opponent, #t.edescr as 'Description', sum(s.pmin) as 'Play Min',
# sum(case e.etype_id
#   when 1 then 70-s.pmin
#   when 2 then null
#   when 3 then 60-s.pmin
#   when 4 then 60-s.pmin
# end) as 'Bench Min',
# sum(s.Goals) as 'Goals', sum(s.Assists) as 'Assists', sum
#(s.ycards) as 'Yellow Cards', sum(s.rcards) as 'Red Cards'
#FROM event e
#join etype t on e.etype_id = t.etype_id
#join opponents o on e.opponent_id = o.opponent_id
#join stats s on e.e_id = s.e_id
#right join players p on p.player_id = s.player_id
#where e.etype_id != 2
#group by p.plast, p.pfirst
#order by p.plast, p.pfirst, e.date, e.begin;

I'm sure it has something to do with the CASE statement inside the SUM function, but I'm not sure why it works in MySQL, but not FronTPage.

If it helps, what I'm trying to do is calculate a column based on another column (Bench Minutes = X - Play Minutes) where the value of X is based on etype_id and then present the sum of that column with the sums of the other columns in a row for an individual.

Any suggestions would be greatly appreciated!

Thanks in advance!
Dale

RE: Database Results Error

(OP)
Update:
It appears it has nothing to do with the CASE statement but the use of GROUP BY and/or the aggregate SUM function.  I've simplified the query to the following and still receive the error.

SELECT
p.plast as 'Player Last',
p.pfirst as 'Player First',
sum(s.pmin),
sum(s.Goals),
sum(s.Assists),
sum(s.ycards),
sum(s.rcards)
FROM players p
join stats s on p.player_id = s.player_id
group by p.plast

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close