Microsoft KB Archive/69304

{| = Using SQL Server COMPUTE, COMPUTE BY Clauses from Within Q+E =
 * width="100%"|

Last reviewed: November 2, 1994

Article ID: Q69304

SUMMARY
Q+E supports the use of the SQL Server COMPUTE clause, but not the COMPUTE BY clause. Attempting to use the COMPUTE BY clause from the SQL query box will result in the following error message being displayed:

Q+E Warning: Extra token at end of clause: BY If Q+E is used to execute stored procedures on the SQL Server that return results generated by either the COMPUTE or COMPUTE BY clauses, the results will be returned. However, all total fields returned will contain the value zero. The above restrictions can be explained by the fact that the COMPUTE BY clause and the ability to create stored procedures are Transact SQL enhancements (not standard SQL). Because Q+E was designed as a front-end for a number of relational database management systems, which may or may not include these enhancements, support for the BY clause was not added to the current version.

MORE INFORMATION
The SQL Server COMPUTE clause is designed to generate grand totals, grand counts, and so on. An example of using the COMPUTE statement is listed below:

select type, pub_id, price from titles where type = &quot;psychology&quot; compute sum(price) When using Q+E, issuing this query from the SQL query box returns the correct results. These results are listed below: type          pub_id      price

psychology    0736         7.00 psychology    0736         7.99 psychology    0736        10.95 psychology    0736        19.99 psychology    0877        21.59 67.52 However, as noted above, if this query is contained within a stored procedure on the server and is executed from Q+E, the computed sum will return the value zero. An example of the results that would be returned in this case are as follows: type          pub_id      price

psychology    0736         7.00 psychology    0736         7.99 psychology    0736        10.95 psychology    0736        19.99 psychology    0877        21.59

0 The SQL Server COMPUTE BY clause breaks a column into subgroups and applies the specified row aggregate function to each group. A sample COMPUTE BY statement is listed below: select type, pub_id, price from titles where type = &quot;psychology&quot; or type = &quot;mod_cook&quot; compute sum(price) BY type When using SAF or ISQL to issue this query, the following results are returned: type          pub_id      price

mod_cook      0877         2.99 mod_cook      0877        19.99 sum ---                              22.98

psychology    0736         7.00 psychology    0736         7.99 psychology    0736        10.95 psychology    0736        19.99 psychology    0877        21.59 sum ---                              67.52 As noted above, the query that contains the COMPUTE BY clause will generate an error message if an attempt is made to send it from the SQL query box in Q+E. However, executing from Q+E a stored procedure on the server that contains this query returns the following results: type          pub_id      price

mod_cook      0877         2.99 mod_cook      0877        19.99

0

psychology    0736         7.00 psychology    0736         7.99 psychology    0736        10.95 psychology    0736        19.99 psychology    0877        21.59

0 As in the case of the COMPUTE clause, the totals are returned as zero. However, if you intend to import this data into an Excel worksheet, the subtotals can be added at a later date.