Microsoft KB Archive/69130

INF: Using SQL Server COMPUTE and COMPUTE BY from Within Q+E

PSS ID Number: Q69130 Article last modified on 01-06-1994

1.10 1.11 4.20 OS/2

= 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 results 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 0 (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 COMPUTE 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. The following is an example of using the COMPUTE statement: select type, pub_id, price from titles where type = “psychology” compute sum(price) When using Q+E, issuing this query from the SQL query box returns the correct results: 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 0. For example: 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. The following is an example of the COMPUTE BY statement: select type, pub_id, price from titles where type = “psychology” or type = “mod_cook” 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 a stored procedure on the server that contained this query from Q+E will return 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 is the case with the COMPUTE clause, the totals are returned as 0 (zero). However, if you intend to import this data into an Excel worksheet, the subtotals may easily be added at a later date.

= Additional reference words: 1.10 1.11 4.20 Transact-SQL =

Copyright Microsoft Corporation 1994.