Using SQL Server COMPUTE, COMPUTE BY Clauses from Within Q+E

 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 = "psychology"
   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 = "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 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.

REFERENCES

"Q+E for Microsoft Excel User's Guide." Version 3.00, page 127.

KBCategory: kbother
KBSubcategory:

Additional reference words: noupd

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.