AppNote: A Practical Guide to Q+E, Part II (WE0821)

Example 2

To see only the unique stor_ids and their corresponding quantities:

1. After you follow the steps in Example 1, select the ord_num, date, qty, payterms, and title_id fields.

2. From the Layout menu, choose Remove Column.

3. From the Select menu, choose Distinct.

     | stor_id  |  sum(qty)

   1 |  6380    |      8
   2 |  7066    |    125
   3 |  7067    |     90
   4 |  7131    |    130
   5 |  7896    |     60
   6 |  8042    |     80

JOINING TABLES

When You're Working with Large Databases

You may find that joining and saving large files or tables is very slow. Below are some suggestions for ways that you may be able to optimize both your computer and your task to speed things up:

• Verify that your tables are truly relational, with no duplicate records or keys.
• Run a compression utility on your hard disk.
• Verify that you have 3-4 megabytes (MB) of hard disk space available.
• Scroll to the end of each table (to do this, press CTRL+END) before joining the files.
• Direct your SET TEMP statement to an adequate RAMDRIVE.SYS file.
• Load the large tables to a RAMDRIVE.SYS file before opening them in Q+E.

When You're Working with More Than One Table at a Time

Q+E lets you display data from multiple database tables in one Query window using the Join command. To join two database tables in a single Query window, the two tables must have at least one column in common. When you join two tables, you join the data in one window (the source) with data in the destination or active window.

You may need to join two tables if you want to display certain information that is not contained in a single table. For example, you might have a master table that contains employee last names and employee numbers and a Badge table that contains employee numbers and badge numbers to allow for employees who have more than one badge number.

If you want to create a query that displays last names and badge numbers of employees, you must join the tables. You can join tables by choosing either the Join command or the Outer Join command from the Select menu. Most of the time you will want to use the Join command. The differences between Join and Outer Join are shown in the following table.

   Command      Result

   Join         Discards all records in the destination
                window that don't have matching records in
                the source window

   Outer Join   Shows all records from the destination
                window whether they match or not

1. To open the database tables you want to join, choose Open from the File menu.

2. From the Window menu, choose Arrange All to view both files at once.

3. In the source file, select the column you want to match.

4. In the destination file, select the column that matches the source file's column.

5. From the Select menu, choose Join.

UPDATING YOUR QUERY

Q+E does not monitor the DBMS you are querying. This means that changes made by other users will not be reflected until you change your query in a way that causes Q+E to reread the records. If you want to force Q+E to refresh the query, choose Query Now from the Select menu.

USING Q+E WHEN YOU CREATE MAILING LABELS

A common misconception is that you can print or view mailing labels in Q+E. However, Q+E merely creates a text file (with the .LAB extension) based on the records and options you specify. To print or view the file, you can open this text file in a text editor, such as Notepad, or a word processor. You will get better results if you use a word processor, such as Word or WordPerfect(R), because the robust text filters in these word processors are more likely to preserve the correct layout than a simpler program such as Write or Notepad. If the text does not come out the way you like, experiment with different ways to import the text. For example, you will get the best results in Microsoft Word when you use the Text With Layout option when you open the .LAB file.

                   APPENDIX A--DOCUMENTATION ERRORS
                   ================================

Chapter 4:   "Saving a Query, Saving Results, and Printing"

   Page 28   Opening a query file
             --------------------
             Step 2. "Select the Query File" should read "Select
             Queryfile as the source."

   Page 28   Saving results to a new database file
             -------------------------------------
             Step 2. "Select the database format..." should read
             "Select the database format in the Destination
             box...."
             Step 4. "...turn on Use Headings for Field Names check
             box." This option is not available for Mailing Labels,
             PrintToFile, or QueryFile.

   Page 32   Printing to a file
             ------------------
             Step 3. "To set the page width...." This statement is
             ambiguous. You must specify the number of characters
             for the page width instead of setting the page width
             in inches.

Chapter 5:   "Editing the Current SQL Statement"

   Page 36   It's a good idea to include the source with all
             SQL SELECT statements. Page 36 has a list of each source
             and the name of the corresponding driver. The following
             example shows how to query dBASE-compatible files:

                select * from dBASEFile | c:\excel\qe\EMP.DBF

              This list should include the following new drivers.

                To query this source         Add this prefix
             ---------------------------------------------------

                Oracle                       Oracle|
                DEC RDB                      RDB|
                dBASE                        dBASEFile|
                Text file                    TextFile|
                SQL Server                   SQLServer|
                Microsoft Excel              ExcelFile|
                OS/2 1.3 Extended Edition    EEDataMgr|

             OS/2 is case-sensitive and can distinguish between
             "SQLServer" and "sqlserver." Please attempt to match the
             case of these drivers exactly when using OS/2.

             The documentation doesn't make it clear that some
             drivers aren't available in both environments. The OS/2
             Extended Edition database driver is available only with
             Q+E for OS/2 1.3. The Oracle and DEC RDB drivers are
             available only with Q+E for Windows. All other drivers
             are available in both environments.

Chapter 6:   "Editing Records"

   Page 37   Allowing editing
             ----------------
             You cannot edit Microsoft Excel files or Text files, so you
             cannot choose the Allow Editing command when working with
             one of these file types. Also, the Select Distinct command
             is not available with the dBASE File, Excel File, or Text
             File file types.

Chapter 8:   "Transferring Data to Other Applications"

   Page 49   The statement, "However, the data remains linked to the
             underlying database through Q+E," is incorrect. The data is
             not linked to the underlying database, but instead is
             linked directly to Q+E.

   Page 52   Linking a Microsoft Excel worksheet to an SQL statement
             "If the query text is greater than 127 characters,
             Microsoft Excel cannot automatically start Q+E to access
             the data." This statement is incorrect. Microsoft Excel has
             a limit of 255 characters per cell, so pasted data will be
             unavailable if the total DDE statement, including braces
             ({ }), is longer than 255 characters. As long as the total
             DDE statement does not exceed 255 characters, Microsoft
             Excel can automatically restart Q+E.

Chapter 9:   "Using Microsoft Excel to Access Data on External
             Databases"

   Page 57   The instructions listed for automatically starting Q+E
             in Microsoft Excel apply only to Microsoft Excel version
             3.0. To automatically start Q+E in Microsoft Excel 4.0, use
             the Add-in Manager to add the QE.XLA file located in the QE
             subdirectory (this subdirectory should be located in the
             directory where you installed Microsoft Excel). The Q+E
             Add-in menu commands will then be available in Microsoft
             Excel.

   Page 66   Database functions such as DSUM() do not work with
             external databases.

   Page 68   DB.DELETE()
             -----------
             In earlier versions of the "User's Guide," the heading
             shows only the question form of the function. There is a
             standard form as well. Also, change DB.DELETE?() to
             DB.DELETE()--no question mark--in the second-to-last line
             in the paragraph below the heading. DB.DELETE?() asks for
             confirmation from the user to delete all records and
             reports any errors, DB.DELETE() does not.

             DB.EXTRACT?()
             -------------
             Returns an error message if no records are found.

             DB.EXTRACT()
             ------------
             Does not return an error message.

   Page 69   DB.LOGON()
             ----------
             The "source" is the only logon parameter that will work
             with DB.LOGON. (The Q+E for Microsoft Excel "User's Guide"
             listed additional parameters, but they did not actually
             work.) In order to pass the server name, the user ID, and
             the password, you must use the Send.Keys command.

             Send.Keys is a Microsoft Excel macro command that passes
             through keystrokes using the shortcut keys. It is not a
             preferred method for running macros, but in this case, it
             is the only method that will work. Documentation for
             Send.Keys is located in your Microsoft Excel "Function
             Reference" under "Send.Keys," and the definitions are
             located under "On.Keys." Send.Keys commands are sent to a
             buffer, so always place them before the commands they will
             execute.

             For example, to log Pam into Testserv with the password of
             spam, use the following code:

                =SEND.KEYS("%STestserv%Lpam%Pspam~")
                =DB.LOGON("sqlserver")

             NOTE: Use the Logon command for SQL Server; additional
             parameters can be passed using this command.

   Page 70   DB.SET.DATABASE()
             -----------------
             You may need to execute DB.LOGON before the source you
             specify is available.

   Page 71   DB.PASTE.FIELDNAMES()
             ---------------------
             The second argument should read "paste_fields", not
             "fieldname". Also, when using this function, use one or the
             other argument (or neither), but not both.

   Page 71   DB.SQL.QUERY()
             --------------
             If you use Type 2 or 3, be certain to enter the query
             argument in double quotation marks:

                =DB.SQL.QUERY(3,"d:\excel\qe\QUERY1.QEF",1)

   Page 74   In the sample Microsoft Word macro near the center
             of the page, the statement

                chan=DDEInitiate("QE","SELECT LAST_NAME, HIRE_DATE
                FROM EMP.DBF")

             should be changed to:

                chan=DDEInitiate("QE","SELECT LAST_NAME, HIRE_DATE
                FROM dBASEFile|EMP.DBF")

             NOTE: The code above is entered all on one line with a
             space between "FROM" and "dBASEFile."

Chapter 11   "Q+E Execute Command Reference"

  Page 100   LOGON()
             -------
             There are undocumented parameters in the Logon command in
             Q+E version 3.0a that can be used with SQL Server.

              - Logon(dbname_Text,Connection_Text,Match_Previous,
                Show_Dialog)

              - dbname_Text = ServerType ()

              - Connection_Text = special keywords used to indicate
                connection parameters necessary to log on.

              - Keywords for the connection string include:

                   SRVR=name_of_the_server
                   UID=user_name
                   PWD=password
                   DB=database_name

              - Match_Previous = TRUE indicates that if the user has
                previously logged on to the same server, then use that
                connection. FALSE means start a new connection every
                time.

              - Show_Dialog = TRUE indicates that the Logon dialog box
                should be displayed.

             Example:

                =EXECUTE(chan,"[logon('SQLServer','SRVR=MYSVR;
                UID=guest;PWD=guest', TRUE,FALSE)]")

             NOTE: The example code is entered all on one line with a
             space between "PWD=guest'," and "TRUE."

  Page 101   OPEN()
             ------
             None of the optional parameters, except for the second one
             (source), will work with any OPEN version.

  Page 105   PASTE()
             -------
             The example should include a closing right parenthesis at
             the end of each line.

  Page 109   SAVE.LABELS.AS()
             ----------------
             The example produces a label with no spaces between
             FIRST_NAME and LAST_NAME or between STATE and ZIP. To
             create a label with proper spacing, use the following
             format instead:

                SAVE.LABEL.AS('ADDR.LAB','FIRST_NAME+'' ''+LAST_NAME;
                CITY+'',''+STATE+'' ''+ZIP',5,1,0,0,0)


             NOTE: This code must be entered all on one line with no
             space between the semicolon after LAST_NAME and CITY. Note
             also that the comma between CITY and STATE in the label
             definition appears to be enclosed in double quotation
             marks. It is actually enclosed in two sets of single
             quotation marks.


Appendix C   "Using Q+E with Microsoft Excel Worksheet Files"

  Page 143   "Opening a Microsoft Excel Worksheet in a Query Window"
             The second paragraph states that Q+E prompts you for a
             password if the worksheet is protected. This information is
             incorrect: there is no prompt, and Q+E is unable to load
             the file. To use this worksheet, you must unprotect it in
             Microsoft Excel first.


                        APPENDIX B--SQL BASICS
                        ======================

WHAT SQL IS

SQL is a standard database language used to query and manage relational databases. Pronounced "sequel" by some and "S-Q-L" by others, SQL is the database language of choice for the vast majority of client-server database management systems. SQL is a comprehensive language for controlling and interacting with a DBMS. SQL is appropriate for a wide range of tasks, from casual database querying to administration and programming. In this document, we will look at using SQL as a query tool only--not as a programming tool.

WHAT SQL IS NOT

SQL is not a complete computer language like COBOL or C. There are no IF statements for testing conditions and no DO or FOR statements for looping. Instead, SQL is a database sublanguage, consisting of about 30 statements specialized for data management tasks. These SQL statements are embedded in a host language, such as C, to extend that language for use in database access.

The "structured" part of SQL is a little misleading, because it isn't particularly structured, especially when compared to highly structured languages such as C or Pascal. Instead, SQL statements resemble simple English sentences with commonly understood keywords.

BASIC SYNTAX OF AN SQL QUERY

In the following examples, the SELECT statement is used to express an SQL query. Every SELECT statement produces a table of query results containing one or more columns and zero or more rows of data. The FROM clause specifies the tables containing the data to be retrieved by a query. The WHERE clause selects the rows to be included in the query results by applying a criterion to rows of the database.

The SQL SELECT statement is used to retrieve data. When SQL is used interactively, SELECT is the core of most queries. A SELECT statement operates on a table (or tables) and produces another table containing the results of the query.

The SELECT statement specifies the columns you want to retrieve. The FROM clause specifies the tables in which the columns are located, and the WHERE clause specifies the rows in the table you want to see. The basic syntax of the SELECT statement is:

   SELECT columns
   FROM tables
   WHERE search_conditions

The following is an example of a SELECT statement in its simplest form:

   SELECT * FROM employee

   SELECT EmpNum, Name, Weight FROM employee

   EmpNum    Name      Weight
   398       Smith     178
   402       Jones     124
   839       Brown     155
   118       Smith     140

   SELECT weight FROM employee

   Weight
   178
   124
   155
   140

   SELECT name, weight FROM employee
   WHERE weight > 150

   Name      Weight
   Brown     155
   Smith     178

   =    Equal to
   !=   Not equal to
   <    Less than
   <=   Less than or equal to
   >    Greater than
   >=   Greater than or equal to

   SELECT name, weight FROM employee
   WHERE weight >= 150 AND weight <= 160


                     APPENDIX C--EXECUTE EXAMPLES
                     ============================

   INITIATE()
   REQUEST()
   FETCH()
   EXECUTE()
   TERMINATE()

For this section, the following assumptions have been made:

• QE.EXE is located in a directory listed in your PATH statement.
• You have installed the dBASEFile and ExcelFile drivers.
• The dBASEFile and TextFile drivers are located in a directory listed in your PATH statement.
• You have not deleted or modified the following sample files, which are located in the QE subdirectory:

        ADDR.DBF
        DEPT.DBF
        EMP.DBF
        LOC.DBF
        EMP.XLS
  

  NOTE: If Q+E and the sample files are not located in the C:\EXCEL\QE directory, you will need to modify many of the following SELECT and OPEN commands to reflect the actual location of Q+E and the sample files.

ADD.CONDITION()

Select employees who are earning less than $30,00 and who are exempt.

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF')]")
   =EXECUTE(chan,"[select.column('SALARY')]")
   =EXECUTE(chan,"[add.condition(1,3,30000,FALSE)]")
   =EXECUTE(chan,"[select.column('EXEMPT')]")
   =EXECUTE(chan,"[add.condition(1,1,'Y',FALSE)]")
   =TERMINATE(chan)
   =RETURN()

COLUMN.WIDTH()

Format the column FIRST_NAME.

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','ExcelFile')]")
   =EXECUTE(chan,"[select.column('FIRST_NAME')]")
   =EXECUTE(chan,"[column.width(30,false)]")
   =TERMINATE(chan)
   =RETURN()

Send a SELECT statement to a Q+E buffer.

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[command(1,'[open(''select * from ')]")
   =EXECUTE(chan,"[command(2,'dBASEFile|')]")
   =EXECUTE(chan,"[command(3,'c:\excel\qe\ADDR.DBF'')]')]")
   =TERMINATE(chan)
   =RETURN()

DEFINE.INDEX()

Create a unique index on EMP.DBF.

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[define('c:\excel\qe\EMP.DBF','dBASEFile')]")
   =EXECUTE(chan,"[define.index('test.ndx','','last_name',
    TRUE,FALSE,1)]")
   =TERMINATE(chan)
   =RETURN()

chan=INITIATE("QE","System")

Copy all records from a SQL Server table to a separate sheet named "QEXAMPLE.XLS."

   =EXECUTE(chan,"[Logon('SQLServer')]")
   =EXECUTE(chan,"[OPEN('use pubs; select * from
    pubs.dbo.sales','SQLServer')]")
   NR=REQUEST(chan,"NUMROWS")
   NC=REQUEST(chan,"NUMCOLS")
   =EXECUTE(chan,"[fetch('Excel','qexample.xls','R1C1:R"&NR&"C"&NC&"',
    'ALL')]")
   =TERMINATE(chan_num)
   =RETURN()

   chan=INITIATE("QE","System")
   =EXECUTE(chan,"[open('c:\excel\qe\LOC.DBF')]")
   =EXECUTE(chan,"[select.column('LOC_ID')]")
   =EXECUTE(chan,"[sort.ascending()]")
   row_found=MATCH("L23",REQUEST(chan,"C1:C1"))
   =EXECUTE(chan,"[fetch('Excel','qexample.xls','R1C1:R1C3''
    R"&row_found&"')]")
   =TERMINATE(chan)
   =RETURN()

JOIN()

Join EMP.DBF and DEPT.DBF.

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','dBASEFile')]")
   =EXECUTE(chan,"[select.column('EMP_ID')]")
   =EXECUTE(chan,"[open('c:\excel\qe\DEPT.DBF','dBASEFile')]")
   =EXECUTE(chan,"[select.column('MGR_ID')]")
   =EXECUTE(chan,"[join()]")
   =TERMINATE(chan)
   =RETURN()

KEYS()

Update the first record in EMP.DBF by sending keystrokes.

   chan=INITIATE("qe","select * from dBASEFile|c:\excel\qe\EMP.DBF")
   =EXECUTE(chan,"[allow.edit(TRUE)]")
   =EXECUTE(chan,"[select.area('R1C1')]")
   =EXECUTE(chan,"[keys('Tami{tab}Sanders{tab}E99999')]")
   =TERMINATE(chan)
   =RETURN()

   increase=INPUT("Enter the percentage to increase salaries
    by:",1)/100
   chan=INITIATE("qe","select * from dBASEFile|c:\excel\qe\emp.dbf")
   =EXECUTE(chan,"[allow.edit(TRUE)]")
   NR=REQUEST(chan,"NUMROWS")
   =FOR("count",1,NR)
   =EXECUTE(chan,"[select.area('R"&count&"1C5')]")
   =REQUEST(chan,"R"&count&"C5")
   =EXECUTE(chan,"[select.area('R"&count&"C5')]")
   =EXECUTE(chan,"[keys('"&A155*increase+A155&"')]")
   =NEXT()
   =TERMINATE(chan)
   =RETURN()

OPEN()

Open a dBASE file.

   =EXECUTE(chan,"[open('c:\excel\qe\DEPT.DBF','dBASEFile')]")
   =EXECUTE(chan,"[select.column('MGR_ID')]")

   chan=INITIATE("QE","System")
   =EXECUTE(chan,"[OPEN('c:\excel\qe\ADDR.DBF','dBASEFile')]")
   =TERMINATE(chan)
   =RETURN()

   chan=INITIATE("QE","System")
   =EXECUTE(chan,"[OPEN('select * from
   c:\excel\qe\EMP.XLS','ExcelFile')]")
   =TERMINATE(chan)
   =RETURN()

   chan=INITIATE("QE","System")
   =EXECUTE(chan,"[Logon('SQLServer')]")
   =EXECUTE(chan,"[OPEN('use pubs; select * from
    pubs.dbo.sales','SQLServer')]")
   =TERMINATE(chan)
   =RETURN()

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[LOGON('EEDataMGR')"])
   =EXECUTE(chan,"[OPEN('SELECT * FROM USERID.STAFF')]")
   =TERMINATE(chan)
   =RETURN()

OPEN.INDEX()

Define an index.

   chan=INITIATE("QE","SYSTEM")
   =EXECUTE(chan,"[open('c:\excel\qe\EMP.DBF','dBASEFile')]")
   =EXECUTE(chan,"[open.index('c:\excel\qe\EMPHIRE.NDX',FALSE)]")
   =EXECUTE(chan,"[use.index('emphire')]")
   =TERMINATE(chan)
   =RETURN()

PASTE.APPEND()

Append a new record to LOC.DBF.

   new_code=INPUT("Enter a new location code:",2)
   new_city=INPUT("What city is this new code for ?",2)
   new_state=INPUT("What state ?",2)
   chan=INITIATE("QE","System")
   =EXECUTE(chan,"[open('c:\excel\qe\LOC.DBF','dBASEFile')]")
   =EXECUTE(chan,"[Allow.Edit(true)]")
   =EXECUTE(chan,"[Paste.Append('"&new_code&CHAR(9)&new_city&CHAR
    (9)&new_state&"')]")
   =TERMINATE(chan)
   =RETURN()

SQL.QUERY()

Send an SQL query to Q+E.

NOTE: The SQL Query option is not available in Q+E until a query has been opened. However, you can enter a query by selecting the SQL option in the Open dialog box.

   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[open('select * from c:\excel\qe\EMP.DBF WHERE
    salary > 30000')]")
   =EXECUTE(chan,"[sql.query('select * from c:\excel\qe\ADDR.DBF where
    state=''NC''')]")
   =TERMINATE(chan)
   =RETURN()

   gstate=INPUT("Which state would you like to limit the query to: NC,
    WA, or CA ?",2)
   chan=INITIATE("qe","system")
   =EXECUTE(chan,"[open('c:\excel\qe\ADDR.DBF','dBASEFile')]")
   =EXECUTE(chan,"[sql.query('select * from c:\excel\qe\ADDR.DBF where
   STATE=''"&gstate&"''')]")
   =TERMINATE(chan)
   =RETURN()

                      APPENDIX D--OTHER EXAMPLES
                      ==========================

STORED PROCEDURES

Stored procedures are collections of SQL statements and control-of- flow language. Stored procedures are compiled the first time they are executed, and their execution plans are stored, dramatically improving the performance of SQL statements and batch processes.

There are two ways to execute a stored procedure from inside Q+E:

Method 1

Log on to SQL Server. From the File menu, choose Open and choose the SQL button. You can type an SQL command directly into this dialog box. For example, type:

      USE pubs; sp_helpjoins 'publishers', 'authors'

Choose the OK button. Any results generated by the stored procedure will be returned to a blank query file in Q+E.

Method 2

After opening an SQL Server table, choose SQL Query from the Select menu. Type the command for the stored procedure as shown in the example above.

EXECUTING A STORED PROCEDURE FROM A MICROSOFT EXCEL MACRO

Stored procedures can also be executed as part of a Microsoft Excel macro. This is done by using the DDE EXECUTE function or by using QE.XLA add-in macro sheet functions. The following are examples of both methods:

   =EXECUTE(chan,"[open('use pubs; sp_helpjoins ' ' ' ' '
   authors' ' ')]")

   -or-

   =DB.SQL.QUERY(2,"use pubs; sp_helpjoins ' 'publishers' ' ' '
   authors' '",1,false)

TO OBTAIN THIS APPLICATION NOTE

The following file(s) are available for download from the Microsoft Software Library:

 ~ WE0821.EXE (size: 62905 bytes) 

   ARTICLE-ID: Q119591
   TITLE     : How to Obtain Microsoft Support Files from Online Services

   http://www.microsoft.com/worldwide/default.htm