OVERVIEW
========
This is one of two Application Notes designed to help you use Q+E and Microsoft Excel together to efficiently manipulate your data. The first Application Note, "A Practical Guide to Q+E, Part I," covers the most commonly asked questions about Q+E. This Application Note provides additional examples and contains answers to specific questions about a variety of Q+E issues. Both Application Notes assume that you know how to use Microsoft Excel and that you have a high level of understanding of the Microsoft Excel macro language.
TABLE OF CONTENTS
=================
Introduction Logging On to Your Database
ORACLE(R) DEC(TM) RDB SQL SYBASE(R) SQL Server
Problems You May Encounter When You Use Q+E
Can't Use Allow Editing Command on Novell Network
To give a user privileges to create and erase files in a
directory above the QE.EXE file
Q+E Prompts You to Log On When You Choose Open or Define
Can't Hide the Logon Dialog BoxProblems Specific to the SQL Server Driver
Multiple Logon Screens as Default Driver Problems Viewing Tables Unable to Edit Data or Records Are Locked
Tips for Using Q+E
Finding Subtotals
Using the ExcelFile, dBASEFile, and TextFile Drivers
Using the SQL Server Driver
Example 1
Example 2
Joining Tables
When You're Working with Large Databases
When You're Working with More Than One Table at a Time
Updating Your Query
Using Q+E When You Create Mailing LabelsAppendix A--Documentation Errors Appendix B--SQL Basics
What SQL Is What SQL Is Not Basic Syntax of an SQL Query
Appendix C--EXECUTE Examples
ADD.CONDITION()
COLUMN.WIDTH()
COMMAND()
DEFINE.INDEX()
chan=INITIATE("QE","System")
JOIN()
KEYS()
OPEN()
OPEN.INDEX()
PASTE.APPEND()
SQL.QUERY()Appendix D--Other Examples
Stored Procedures
Method 1
Method 2
Executing a Stored Procedure from a Microsoft Excel Macro
INTRODUCTION
============
This Application Note addresses some specific problems you may encounter when you use Q+E and offers tips about how to perform specific tasks in Q+E. The appendixes list documentation errors, provide an overview of SQL, and supply examples of dynamic data exchange (DDE) macros that you can use when you use Microsoft Excel and Q+E together.
LOGGING ON TO YOUR DATABASE
===========================
ORACLE
The Q+E ORACLE driver supports a connection to ORACLE 6.0 running on an OS/2(R) platform. To log on to ORACLE do the following:
- Obtain the client software from Oracle and install it on your hard disk. ORACLE provides a directory (called ORACLE) of SQL*Net Services to connect to the server through a variety of different networks.
Add the following line to your AUTOEXEC.BAT file:
set config=c:\oracle\config.ora
Make sure the ORACLE directory is on your path and that Microsoft Excel (or the directory where QE.EXE is located) is on the PATH statement in your AUTOEXEC.BAT file.
Before you start Windows, run the appropriate program for your network at the MS-DOS prompt.
If you are using this network Run this program -------------------------------------------------------- Microsoft LAN Manager SQLNTB.EXE Novell(R) NetWare(R) SQLSPX.EXE DECnet(TM) Pathworks(TM) 4.0 SQLDNT.EXE Banyan(R) VINES(R) SQLVIN.EXE- Start Windows, and then start Q+E.
From the File menu, choose Logon. Fill in the appropriate information in the Logon dialog box. For example, type:
LOGON: <b:MYSERVER> USERNAME: <SCOTT> PASSWORD: <TIGER>
NOTE: You must specify the drive for LAN Manager networks. This drive letter will vary depending on your particular network. If you are unsure which letter to designate, switch to the MS-DOS prompt, change to the ORACLE directory, and type type config.ora to display a local variable that will indicate the drive letter you are to use. The drive letter is case-sensitive. The following table lists some network programs and specifies the drive letters that are typically associated with them.
This network program Uses this drive letter
-------------------------------------------------------
TCP/IP SQLNTB.EXE T
Novell SQLIPX.EXE X
LAN Manager SLQNTB.EXE B
DECnet SQLDNT.EXE D
Neither the Q+E Oracle driver nor SQLNTB.EXE has been tested for
connecting to ORACLE running on a UNIX(R) platform. The Q+E Oracle
driver can connect to ORACLE running on a VAX, but only in standard
mode.
DEC RDB
To use the DEC RDB driver to access an RDB database, you must first install and run DECnet services to connect to the data. See your DEC RDB database administrator or RDB manual for details, or contact Digital Equipment Corporation for the proper DECnet files.
You can trace most problems in connecting with RDB to an invalid schema statement. The schema statement cannot include logical operators and the entire sentence must be spelled out as in the following example:
declare schema filename 'sys$sysdevice:[dbk$personnel]personnel' disk name : sys$sysdevice: directory name : [dbk$personnel] database name : personnel
SQL
In general, you do not need to obtain any special software to log on to Microsoft SQL Server. Three dynamic-link libraries (DLLs) are required to create named pipes to access Microsoft SQL Server. These DLLs include a DB-Library, a Net-Library, and a Network API Library:
- NETAPI.DLL should be located in your network directory and is provided by your network vendor.
- W3DBLIB.DLL is located in your EXCEL directory.
- DBNMP3.DLL is located in your EXCEL directory.
W3DBLIB.DLL and DBNMP3.DLL are shipped with Microsoft Excel 4.0. NOTE: The QEREADME.TXT that shipped with Microsoft Excel advises that you use the SQL Server 4.20.00s versions of W3DBLIB.DLL and DBNMP3.DLL. Do not follow this advice. These versions of the DLLs may cause the Q+E warning "Changed language setting to US_English" when you query SQL tables in Q+E. To avoid this error message, use the DLLs that shipped with Microsoft Excel 4.0. To use these DLLS, do either of the following:
- Make sure that Microsoft Excel is in your PATH statement, and rename the SQL W3DBLIB.DLL and DBNMP3.DLL files to W3DBLIB.BAK and DBNMP3.BAK. -or-
- Upgrade the DB-Library to version 4.20.21. This version is available with the current version of the SQL Server update.
Microsoft Excel ships with version 1.10 of these DLLs, and installs them in the EXCEL directory.
Filename File size Date
W3DBLIB.DLL 130736 bytes 4/2/92 DBNMP3.DLL 8017 bytes 4/2/92
NOTE: These dates reflect the date Microsoft Excel 4.0 shipped; these versions of the DLLs are actually earlier versions than the ones that ship with SQL. SQL Server 4.2 ships with version 4.20.00 of the same DLLs and installs them in the SQL directory.
Filename File size Date
W3DBLIB.DLL 163536 bytes 2/26/92 DBNMP3.DLL 7921 bytes 2/26/92
The text of the QEREADME.TXT that shipped with Microsoft Excel version 4.0a has been revised.
SYBASE SQL SERVER
Connecting to SYBASE SQL Server has not been tested with Q+E. To connect to a SYBASE SQL Server, you need to obtain files from Sybase called Netlibrary and Client Library. These libraries contain a series of Windows and MS-DOS DLLs that allow you to connect to a SYBASE SQL Server.
PROBLEMS YOU MAY ENCOUNTER WHEN YOU USE Q+E
===========================================
CAN'T USE ALLOW EDITING COMMAND ON NOVELL NETWORK
The Allow Editing command on the Edit menu of Q+E may be unavailable (dimmed) when you open a dBASE(R) file stored on a Novell server. (In most cases, you will be allowed to edit the file if you log on with Supervisor privileges. You will also be able to edit the dBASE file if you open it in Microsoft Excel.) The Allow Editing command is unavailable because Q+E cannot create the temporary file that it requires. Q+E will attempt to create the temporary file in the root directory of the drive on which the QE.EXE file resides. If you do not have create and erase privileges for this root directory, Q+E will fail to create a temporary file, and the Allow Editing command will be unavailable.
Normally, you would not want to give a user privileges to create and erase files in the root directory. However, you can use the Novell Map Root command to map the user to a directory above the QE.EXE file where the user can have create and erase privileges. For example, assume that Q+E is on volume SYS on the file server NETONE. The path to Q+E might resemble the following, NETONE/SYS:PROGRAMS/APPS/EXCEL/QE.EXE.
To give a user privileges to create and
erase files in a directory above the QE.EXE file
Use the Novell Map Root command to map the user to the APPS subdirectory:
map root f:=netone/sys:programs/apps
-or-
map root s1:=netone/sys:programs/apps
- Give the user create and erase privileges for the APPS subdirectory.
- Erase any temporary files from F and from the root of the file server.
NOTE: This problem may also occur on Banyan VINES networks.
Q+E PROMPTS YOU TO LOG ON WHEN YOU CHOOSE OPEN OR DEFINE
When you use the SQL Server, OS/2 Extended Edition, Oracle, or DEC RDB database drivers, you must log on to the specific server to manipulate its data.
If you choose this type of source as your default database driver, Q+E prompts you to log on immediately after you choose Open or Define from the File menu. You can either log on or choose the Cancel button. If you want to switch to a source that does not require logging on (ExcelFile, dBASEFile, or TextFile), change the source in your QE.INI file so that it looks like the following (the QE.INI file is located in your Windows directory and should be modified in a text editor such as Notepad):
[Q+E] NoLogon=dBASEFile,TextFile,ExcelFile connect=dBASEFile
On an OS/2 system, this method does not work, and you must reinstall Q+E, taking care not to choose SQLServer as the default driver. Your files will not be affected by this process.
CAN'T HIDE THE LOGON DIALOG BOX
When you run Microsoft Excel macros or use links to connect to remote databases, the Logon dialog box will always be displayed [regardless of any LOGON() statements you use in your code].
When you log on using DDE, it is often preferable that these types of dialog boxes be hidden from the user. However, this Logon dialog box will appear every time you log on to your database management system (DBMS), and there is no way to disable it.
SQL Server is the only exception to this rule. See "Appendix A," starting on page 12 of this Application Note, for the LOGON macro command that can hide the dialog box.
PROBLEMS SPECIFIC TO THE SQL SERVER DRIVER
==========================================
MULTIPLE LOGON SCREENS AS DEFAULT DRIVER
If your default database driver is an SQL Server driver, you may encounter two Logon dialog boxes when you run the Q+E DB.LOGON() or LOGON() macro commands. To properly log on to the server, cancel the second dialog box, and, in your QE.INI file, change the default driver from SQLServer to another driver (for example, use dBASEFile).
PROBLEMS VIEWING TABLES
Tables on a SQL Server machine have a three-part ID. The following table describes these parts.
Part of ID What the part signifies
Database The database where the table is stored Owner The logon ID of the table owner Tablename The name of the table
When you choose Open from the File menu and specify SQLServer as your source, you may not see anything displayed in the Table Name box if you have logged on as a user other than the system administrator or if you have not created any tables. By default, Q+E displays only the tables you have created. To view tables that were created by another user, select that user's name from the Owner list. For example, if you logged on as GUEST, you would not see the sample files in the PUBS database, because the examples were created by the user DBO. For the sample files to be listed in the Table Name list box, select DBO from the Owner list.
UNABLE TO EDIT DATA OR RECORDS ARE LOCKED
When you open a SQL Server table, Q+E retrieves as many records as it needs to fill up the window. As you scroll through the records, Q+E retrieves new records from SQL Server. Q+E will not retrieve the last record from SQL Server until you scroll to the bottom of the list.
As records are retrieved from SQL Server, Q+E copies them to a temporary file, or buffer, on your workstation. When you scroll up, Q+E reads copies of the records from the buffer, not from SQL Server. Similarly, if you scroll down to records that you have already seen, those records are read from the buffer.
While Q+E retrieves records from a SQL Server table, SQL Server places locks on all or part of the table. The locks prevent other users from modifying the records in the table while they are being retrieved. Locks may be held until Q+E has read the last record from the table-- that is, when you have scrolled to the bottom of the window (to scroll to the bottom of the window, press CTRL+END)--or until the window is closed.
If you open a SQL Server table but don't scroll to the bottom of the window, locks will remain on records in the table; these locks may prevent other users from modifying records in the table. You can remove the locks by scrolling to the bottom of the window or by closing the window.
TIPS FOR USING Q+E
==================
FINDING SUBTOTALS
Using the ExcelFile, dBASEFile, Oracle, and TextFile Drivers
The ExcelFile, dBASEFile, Oracle, and TextFile drivers for Q+E do not support the COMPUTE BY or GROUP BY clauses required to find subtotals in an SQL query [COMPUTE BY and GROUP BY are similar to the SUM-IF statement and DSUM() function in Microsoft Excel].
One way to work around this behavior is to create a crosstab table in Microsoft Excel. The following steps use this method to find the total salary of employees in each of the departments in the EMP.DBF sample file:
1.Set Database to External, and select the EMP.DBF sample database.
2.From the Data menu, choose Crosstab to bring up the Crosstab
ReportWizard.
3.In the Crosstab ReportWizard dialog box, choose the Create A New
Crosstab button.
4.Choose the Next button to skip the Row Categories dialog box. 5.In the Column Categories dialog box, select DEPT, and choose the
Add button. Choose the Next button to move to the next dialog box.
6.In the Value Fields dialog box, select SALARY, and choose the Add
button. Choose the Next button to move to the next dialog box.
7.In the Final dialog box, choose the Create It button. Your crosstab table should resemble the following example:
| DEPT |
| D050 | D101 | D190 | D202 | D050 | D101 | D190 | D202 | Grand
| | | | | Sum | Sum | Sum | Sum | totalSum | 68900| 169900| 86900| 86750| 68900| 169900| 86900| 86750| 412450 of | SALARY |
Using the SQL Server Driver
COMPUTE BY is not supported by the SQL Server driver in Q+E. COMPUTE BY is a Transact-SQL statement that is not part of standard SQL. The examples below describe an SQL query that will obtain subtotals using SUM and GROUP BY. This query will not return a report with subtotals after each category the way that COMPUTE BY does when executed from a SQL Server front-end such as SAF. However, the report will list each record, with the corresponding subtotal for its category displayed in an additional field.
The result of the following steps will be a query that selects records from the SALES table in the PUBS database and finds the total sales for each stor_id. To have the SALES table available, you must have installed the PUBS sample database.
Example 1
- Start Q+E.
- From the File menu, choose Logon, and log on to an SQL Server.
- From the File menu, choose Open.
- In the Table Name box, type PUBS.DBO.SALES.
- In the Source box, select SQLServer, and choose the OK button.
- From the Select menu, choose SQL Query.
- After the field name stor_id, type SUM(QTY).
- At the end of the query, type GROUP BY STOR_ID.
The query should now read:
USE pubs; SELECT stor_id, SUM(qty), ord_num, date, qty, payterms, title_id FROM dbo.sales GROUP BY stor_id
Choose the OK button to run the modified query.
Example 2
To see only the unique stor_ids and their corresponding quantities:
- After you follow the steps in Example 1, select the ord_num, date, qty, payterms, and title_id fields.
- From the Layout menu, choose Remove Column.
- From the Select menu, choose Distinct.
The result should resemble the following example:
| 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
The usual procedure for joining database tables is as follows:
- To open the database tables you want to join, choose Open from the File menu.
- From the Window menu, choose Arrange All to view both files at once.
- In the source file, select the column you want to match.
- In the destination file, select the column that matches the source file's column.
- 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
================================
This section identifies a number of documentation errors and ambiguities that you may encounter in the Q+E for Microsoft Excel "User's Guide" that comes with Microsoft Excel 4.0. We've done our best to include them all and to make necessary corrections, but others may exist. If you find what you believe to be a documentation error, or if you find something ambiguous in the documentation, please bring it to our attention.
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)
Chapter 10: "Using Dynamic Data Exchange to Communicate with Q+E"
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
======================
The following information is included in the event that your DBMS has not provided you with descriptions of Structured Query Language (SQL). We have tried to cover only the major points; you may want to consult more complete documentation for further information.
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
You must select at least one column and at least one table in order to have a valid SELECT statement. You can specify more than one column (and/or table) by separating the column (and/or table) names with a comma. The following is an example of a SELECT statement in its simplest form:
SELECT * FROM employee
The asterisk refers to all the columns in the referenced tables--in this case, the Employee table. The above statement is equivalent to:
SELECT EmpNum, Name, Weight FROM employee
Both of these SELECT statements return:
EmpNum Name Weight 398 Smith 178 402 Jones 124 839 Brown 155 118 Smith 140
SELECT is most often used with a number of optional clauses. Most of the time you'll want to narrow the scope of the query to include a specific set of columns. For example, if you wanted to view only the weights of the employees, you would make the following SQL query:
SELECT weight FROM employee
The result is the following:
Weight 178 124 155 140
You might further narrow your query by adding conditions. SQL uses the keyword WHERE to specify which of the available records you want. To list only the names and weights of those employees who weigh more than 150 pounds, use the following SQL query:
SELECT name, weight FROM employee WHERE weight > 150
The result is the following:
Name Weight Brown 155 Smith 178
SQL offers a rich set of search conditions that allow you to specify many different kinds of queries. Only those rows that evaluate to TRUE, based on the comparison, are displayed. In addition to ">", all the comparison operators are supported in SQL Server. They are:
= Equal to != Not equal to < Less than <= Less than or equal to > Greater than >= Greater than or equal to
Using the rules of logic, you can join these simple SQL search conditions to form more complex ones. The search conditions can be combined using the logical conditions AND, OR, and NOT. For example, to find all employees who weigh between 150 and 160 pounds, your query would be:
SELECT name, weight FROM employee
WHERE weight >= 150 AND weight <= 160
APPENDIX C--EXECUTE EXAMPLES
============================
This appendix contains examples of most of the commands documented in Chapter 11 of the Q+E for Microsoft Excel "User's Guide." We highly recommend you read Chapter 10 to understand the basic concepts of dynamic data exchange (DDE) and that you become familiar with the syntax of the following commands:
INITIATE() REQUEST() FETCH() EXECUTE() TERMINATE()
For more information about the syntax of these commands, see the "A Practical Guide to Q+E, Part I" Application Note (WE0500). 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.XLSNOTE: 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()
COMMAND() 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()
Find and copy the location of department L23 to a separate sheet named "QEXAMPLE.XLS."
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 the salaries in EMP.DBF by a user-specified percentage.
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()
Open a Microsoft Excel file with a query.
chan=INITIATE("QE","System")
=EXECUTE(chan,"[OPEN('select * from
c:\excel\qe\EMP.XLS','ExcelFile')]")
=TERMINATE(chan)
=RETURN()
Open a SQL Server table with a query.
chan=INITIATE("QE","System")
=EXECUTE(chan,"[Logon('SQLServer')]")
=EXECUTE(chan,"[OPEN('use pubs; select * from
pubs.dbo.sales','SQLServer')]")
=TERMINATE(chan)
=RETURN()
Open an IBM(R) OS/2 EE table.
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()
Select all employees in NC, WA, or CA.
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()
NOTE: The second execute statement should be entered all on one line with a space between "where" and "state."
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'
-or- 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)
For more information about downloading files from the Microsoft Software Library, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q119591 TITLE : How to Obtain Microsoft Support Files from Online Services
If you are unable to access the source(s) listed above, you can have this Application Note mailed or faxed to you by calling Microsoft Product Support Services Monday through Friday, 6:00 A.M. to 6:00 P.M. Pacific time at (425) 635-7070. If you are outside the United States, contact the Microsoft subsidiary for your area. To locate your subsidiary, see the Microsoft World Wide Offices Web site at:
http://www.microsoft.com/worldwide/default.htm
AppNote: A Practical Guide to Q+E, Part II (WE0821)Last reviewed: October 6, 1997 |
|
The information in this article applies to:
The Application Note "A Practical Guide to Using Q+E, Part II," is now available from Microsoft Product Support Services (PSS). This Application Note addresses some specific problems you may encounter when you use Q+E and offers tips about how to perform specific tasks in Q+E. The appendixes list documentation errors, provide an overview of SQL, and supply examples of dynamic data exchange (DDE) macros that you can use when you use Microsoft Excel and Q+E together. You can obtain this Application Note from the following sources: You can obtain this Application Note from the following sources:
For complete information, see the "To Obtain This Application Note" section at the end of this article. THE TEXT OF WE0821 Microsoft(R) Product Support Services Application Note (Text File)
WE0821: A PRACTICAL GUIDE TO USING Q+E, PART II
Revision Date: 8/94
No Disk Included
The following information applies to Microsoft Excel versions 3.0, 4.0, and 4.0a for Windows(TM). | INFORMATION PROVIDED IN THIS DOCUMENT AND ANY SOFTWARE THAT MAY | | ACCOMPANY THIS DOCUMENT (collectively referred to as an Application | | Note) IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND, EITHER | | EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED | | WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR | | PURPOSE. The user assumes the entire risk as to the accuracy and | | the use of this Application Note. This Application Note may be | | copied and distributed subject to the following conditions: 1) All | | text must be copied without modification and all pages must be | | included; 2) If software is included, all files on the disk(s) must | | be copied without modification (the MS-DOS(R) utility diskcopy is | | appropriate for this purpose); 3) All components of this | | Application Note must be distributed together; and 4) This | | Application Note may not be distributed for profit. | | | | Copyright (C) 1991-1994 Microsoft Corporation. All Rights Reserved. | | Microsoft and MS-DOS are registered trademarks and Windows is a | | trademark of Microsoft Corporation. | | Banyan and VINES are registered trademarks of Banyan Systems, Inc. | | dBASE is a registered trademark of Borland International, Inc. | | DEC, DECnet, Pathworks are trademarks of Digital Equipment | | Corporation. | | IBM and OS/2 are registered trademarks of International Business | | Machines Corporation. | | NetWare and Novell are registered trademarks of Novell, Inc. | | ORACLE is a registered trademark of Oracle Corporation. | | Q+E is a registered trademark of Pioneer Software Corporation. | | SYBASE is a registered trademark of Sybase, Inc. | | UNIX is a registered trademark of UNIX Systems Laboratories. | | WordPerfect is a registered trademark of WordPerfect Corporation. | |
Additional query words: 4.00 WE0500 Last reviewed: October 6, 1997 |
