OS/2 Shareware BBS: Product/Product.zip/visquery.zip/FTB89A.INF

OS/2 Help File | 1994-07-12 | 762KB | 6,410 lines

ΓòÉΓòÉΓòÉ 1. Notices ΓòÉΓòÉΓòÉ

References in this publication to IBM products, programs, or services do not
imply that IBM intends to make these available in all countries in which IBM
operates. Any reference to an IBM product, program, or service is not intended
to state or imply that only that IBM product, program, or service may be used.
Any functionally equivalent product, program, or service that does not infringe
any of IBM's intellectual property rights may be used instead of the IBM
product, program, or service. The evaluation and verification of operation in
conjunction with other products, except those expressly designated by IBM, are
the responsibility of the user.

IBM may have patents or pending patent applications covering subject matter in
this document. The furnishing of this document does not give you any license to
these patents. You can send license inquiries, in writing, to:

IBM Corporation
IBM Director of Licensing
208 Harbor Drive
Stamford, Connecticut 06904-2501
U.S.A.


ΓòÉΓòÉΓòÉ 1.1. Online publications ΓòÉΓòÉΓòÉ

For online information in this book, we authorize you to:

o Copy, modify, and print the documentation contained on the media, for use
  within your enterprise, provided you reproduce the copyright notice, all
  warning statements, and other required statements on each copy or partial
  copy.

o Transfer the original unaltered copy of the documentation when you transfer
  the related IBM product (which may be either machines you own, or programs,
  if the program's license terms permit a transfer). You must, at the same
  time, destroy all other copies of the documentation.

You are responsible for payment of any taxes, including personal property
taxes, rsulting from this authorization.

THERE ARE NO WARRANTIES, EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF
MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE.

Some jurisdictions do not allow the exclusion of implied warranties, so the
above exclusion may not apply to you.

Your failure to comply with the terms above terminates this authorization. Upon
termination, you must destrioy your machine readable documentation.


ΓòÉΓòÉΓòÉ 1.2. Examples ΓòÉΓòÉΓòÉ

This publication contains examples of data and windows used in daily business
operations. They are intended only to be illustrative of the types of functions
available in QUERY FOR OS/2 and may contain the names of individuals,
companies, brands, and products. All of these names are fictitious, and any
similarity to the names and addresses used by an actual business enterprise is
entirely coincidental. Windows shown in this publication are intended to be
examples and may not be exact representations of actual windows.


ΓòÉΓòÉΓòÉ 1.3. Trademarks and service marks ΓòÉΓòÉΓòÉ

The following terms, denoted by an asterisk (*), used in this publication, are
trademarks or service marks of IBM Corporation in the United States of America
or other countries:

AIX                                               OS/2
AIX/6000                                          OS/400
Application System/400                            Presentation Manager
AS/400                                            PS/2
DATABASE 2                                        QMF
DB2                                               SQL/400
DB2/2                                             SQL/DS
DB2/6000                                          Systems Application
                                                  Architecture
Extended Services                                 Ultimedia
IBM                                               Workplace Shell
Operating System/400


ΓòÉΓòÉΓòÉ 2. About this book ΓòÉΓòÉΓòÉ

This book describes the main features of IBM* Visualizer Query for OS/2
(referred to as QUERY FOR OS/2). It includes this preface, three parts,
appendixes, a bibliography, and an index.

The preface covers assumptions about your:

Hardware                                          Database authorizations
Software                                          Structured Query Language
                                                  (SQL) knowledge
OS/2* knowledge

Part 1, Introducing Query for OS/2, describes QUERY FOR OS/2 and additional
Visualizer products that you can add to it.

Producing hands-on information with Query for OS/2 describes some tasks you can
do with QUERY FOR OS/2.

Welcome to Query for OS/2 shows you what you get when QUERY FOR OS/2 is
installed.

Extending Visualizer Query for OS/2 describes products you can add to QUERY FOR
OS/2 to improve and enhance the way you work with and present data.

Part 2, Visualizing data, shows you how to access existing data, query it, and
present it without using SQL.

Looking at and creating SQL tables describes how to access and work with the
data you currently use, and how to create and store new tables in your
database.

Looking at and creating SQL views covers how you can work with views in your
database, and how to combine data from many tables into a single view.

Selecting and combining data using queries details how to use Visualizer
queries that have been created for you, and how to create a simple query of
your own.

Retrieving and manipulating data using queries provides information on using
Visualizer's powerful features to create and run complex queries.

Accessing data using SQL Statements describes how to use an SQL Statement to
retrieve data.

Analyzing and presenting data using reports gives details on analyzing and
presenting data in a report.

Choosing the best report for your data describes how to create the best report
for your data.

Defining and working with Visualizer tables shows you how to work with
Visualizer tables.

Part 3, Accessing and maintaining data with Query for OS/2, describes how to
bring your database to your desktop and maintain the data in it. Some of the
tasks in this part require a basic understanding of SQL.

Accessing SQL databases describes how to access remote and local databases, and
how to display the tables and views in those databases.

Improving data integrity, performance, and security covers how you can improve
the integrity, performance, and security of your tables and views.

Creating effective SQL statements shows you how to define SQL Statements for
use by others.

The appendixes provide information on customizing QUERY FOR OS/2 objects,
running objects from the OS/2 command line, creating and renaming new objects,
and using QMF* forms with QUERY FOR OS/2.


ΓòÉΓòÉΓòÉ 3. Before you start... ΓòÉΓòÉΓòÉ

Before you begin using QUERY FOR OS/2, make sure your workstation meets the
minimum hardware and memory requirements outlined on the Query for OS/2 Quick
Install Card. Also, OS/2 Version 2.1 (or later) and QUERY FOR OS/2 should be
installed on your IBM PS/2* or IBM-compatible workstation. To use non-product
data, we recommend you install DATABASE 2* OS/2 (DB2/2*) Version 1.1 (or
later).

You should know the names of the SQL databases, SQL tables, and SQL views you
are authorized to use, and have some knowledge of the database you are working
with. If you do not know the names of the objects you can use, if you need
additional authorizations, or if you need more information about your database,
see your database administrator.

Using SQL View and SQL Statement objects requires that you have a basic
understanding of SQL. For more information, see the SQL reference for your
database.

You should be familiar with OS/2 and be able to perform the following tasks:

Drag and drop objects                             Copy and paste objects
Open and close windows                            Create shadows of objects
Print objects                                     Display a pop-up menu
Delete objects                                    Access online help
Move objects                                      Open a notebook and change an
                                                  object's settings

You can find basic information about OS/2 by opening the Information folder in
the OS/2 System folder on your desktop. You can then view the following OS/2
information:

Master Help Index                                 Command Reference
Glossary                                          Tutorial


ΓòÉΓòÉΓòÉ 4. Producing hands-on information with Query for OS/2 ΓòÉΓòÉΓòÉ

The best way to understand how QUERY FOR OS/2 can help you produce timely,
useful information is to see it in action. So before you learn about the
individual parts of QUERY FOR OS/2, here is an example of how one person solved
a business problem using QUERY FOR OS/2.


ΓòÉΓòÉΓòÉ 4.1. Show me where my business is doing well ΓòÉΓòÉΓòÉ

Alan Bryant is a regional manager for an international credit card company.
Each month Alan needs to produce a listing that shows the stores in his region
where his company's card is used most. He has to deliver this information to
many people, so he decides to create a "success report".

The information Alan needs is stored in a remote database, so he creates an SQL
database on his desktop and filters it to show only those tables that contain
information about his region.

Alan only wants to use a couple of tables from the database: one table contains
details of transactions at stores in his region, using a code for each store;
the other identifies each store name by using the store codes. By dragging
these tables to a query and linking them, Alan can access the data he wants for
his report-store names and the number of transactions at each store.

Now, Alan drops this query onto a report to see all the stores in his area that
accept his company's card. The report lists the stores in descending order
based on the number of transactions at each store. Alan customizes his report
to look just the way he wants it, using fonts, colors, and headings, then mails
the report electronically to everyone on his distribution list.

The query is rerun whenever the report is opened, so everyone who has a copy of
the report also has the most up-to-date information at their fingertips.


ΓòÉΓòÉΓòÉ 4.2. How did he do that? ΓòÉΓòÉΓòÉ

Alan just created a document that provides the right information to the right
people whenever they need to see it, without ever stepping away from his desk.
You can do this same thing using QUERY FOR OS/2. To find information on the
different objects

To do this:                                                 See:
Create an SQL database and display its contents             Accessing SQL
                                                            databases
Use SQL tables in a database, or create new ones            Looking at and
                                                            creating SQL tables
Create queries without using SQL                            Selecting and
                                                            combining data
                                                            using queries and
                                                            Retrieving and
                                                            manipulating data
                                                            using queries
Create and tailor a report                                  Analyzing and
                                                            presenting data
                                                            using reports and
                                                            Choosing the best
                                                            report for your
                                                            data
Keep reports up-to-date by linking objects                  Choosing the best
                                                            report for your
                                                            data

QUERY FOR OS/2 contains some other objects that Alan could have used in the
preceding example to speed up his work even more.

o To use a view to combine information from the tables in your query, see
  Looking at and creating SQL views

o To store the data from the query in a Visualizer table and take it when you
  visit the stores in your region, see Defining and working with Visualizer
  tables

o To write an SQL Statement (instead of using a Visualizer query) as input for
  your report, see Accessing data using SQL Statements


ΓòÉΓòÉΓòÉ 5. Welcome to Query for OS/2 ΓòÉΓòÉΓòÉ

Start visualizing your data with IBM Visualizer Query for OS/2, a workstation
query and presentation tool for database users. QUERY FOR OS/2 uses a simple
graphical interface so you can improve the way you do business without needing
detailed knowledge of databases or SQL. QUERY FOR OS/2 provides access to a
wide range of databases, so you can get to your corporate, departmental, or
personal data and do your job faster and more efficiently.

QUERY FOR OS/2 is incorporated into OS/2's desktop interface, so you can do
your work by using icons on your workstation screen that represent objects you
already use in your day-to-day activities.

The SQL Table, Query, and Report icons on your screen represent actual tables,
queries, and reports. This means you can query your data and create reports
without using difficult syntax or learning complex commands. For example, you
can:

o Print a report by simply dragging its icon to a printer icon.

o Query data in several tables by dragging the small icons for the tables to an
  open query.

o Update data by opening a table icon and typing over the existing data.

o Keep a report constantly up-to-date by linking it to a query-whenever you
  open the report, the linked query runs again and updates the report with the
  latest information.

With QUERY FOR OS/2, you can do your work faster by moving, copying, or linking
objects on the screen. When you open any QUERY FOR OS/2 object, you can do
everything by pointing and selecting with your mouse. There is a convenient
tool bar for things you do often. And QUERY FOR OS/2 also includes extensive
online documentation to help you with the wide variety of tasks you may want to
perform.

QUERY FOR OS/2 provides a set of integrated objects representing a wide variety
of information, so you can organize and do your work in ways that suit you.

Access the contents of a relational database from your desktop with Visualizer
SQL Database. You can view its contents, submit SQL, or access other databases.

View or change the structure or data of a table in a database with Visualizer
SQL Table.

Look at a view in a database or combine data from SQL tables with Visualizer
SQL View. You can also create a new SQL view using an existing SQL view.

Keep working when you're away from your database or using a laptop computer
with Visualizer Table. You can import data into Visualizer tables, export data
from Visualizer tables, or create new SQL tables by dropping Visualizer tables
into an SQL database.

Join, select, and summarize data in SQL tables, SQL views, and Visualizer
tables with Visualizer Query.

Create, store, and run queries with Visualizer SQL Statement. Users with little
or no SQL knowledge can use SQL Statements that are already created for them,
and users familiar with SQL can create complex queries quickly.

Present your data using fonts and colors with Visualizer Report. Your report
can use data from SQL tables, SQL views, queries, Visualizer tables, and SQL
Statements.


ΓòÉΓòÉΓòÉ 5.1. The Visualizer folder ΓòÉΓòÉΓòÉ

All the tools that you need to start using QUERY FOR OS/2 are in the Visualizer
folder, which is displayed on your desktop when you install QUERY FOR OS/2.
When you open the folder, you'll see something like this:

When you install QUERY FOR OS/2, an SQL database object is created for each
database cataloged in DB2/2. These objects help you work with your data quickly
and easily. When you open an SQL Database object, the tables and views you can
work with are displayed. Your data is at your fingertips-just click the mouse a
few times and your data is displayed on your screen.

The SQL Database object can retrieve data from the following database managers:

DB2/2                                             DB2/VM (SQL/DS*)
DB2/6000*                                         DB2/VSE (SQL/DS)
DB2* on MVS                                       AS/400* Database

The other folders and objects in the Visualizer folder are described below:

Start Here provides an overview and scenarios showing how to use QUERY FOR OS/2
"in the real world".

You can find tutorials that help you start using QUERY FOR OS/2 immediately in
the Tutorials folder.

A folder holds samples of QUERY FOR OS/2 objects, showing you how you might use
them in your organization.

A folder contains online versions of the product manuals.

The Profile maintains settings used by all QUERY FOR OS/2 objects. These
settings include time, date, and language.

This object, found in the Utilities folder, helps you maintain QUERY FOR OS/2,
install new versions of QUERY FOR OS/2, or delete QUERY FOR OS/2 from your
workstation.

This tool, which is also in the Utilities folder, binds databases that you
catalog after installing QUERY FOR OS/2.

A folder contains templates of the QUERY FOR OS/2 objects. The original
templates are located in the OS/2 Templates folder.


ΓòÉΓòÉΓòÉ 5.2. Query for OS/2 templates ΓòÉΓòÉΓòÉ

The Shadows of Templates folder contains templates for each of the objects. You
can create these objects by dragging and dropping them onto a folder or SQL
database.

Common OS/2 tasks gives details on creating and renaming objects.


ΓòÉΓòÉΓòÉ 5.3. Query for OS/2 online help ΓòÉΓòÉΓòÉ

Comprehensive online help is available from every window in QUERY FOR OS/2. To
get online help, do one of the following:

o Press function key 1 (F1) for help on a selected object, an active window, or
  a highlighted choice in a menu.

o Select the Help push button in a QUERY FOR OS/2 window.

o Select a choice from the Help menu.

o Select from the tool bar.


ΓòÉΓòÉΓòÉ 6. Extending Visualizer Query for OS/2 ΓòÉΓòÉΓòÉ

QUERY FOR OS/2 is at the heart of the IBM Visualizer family of products. If you
use QUERY FOR OS/2 to access and query your data, you can also use it as a
foundation for a wide range of tools, or as a companion to other products, that
improve the way you do business.

To order any of these products, please contact your IBM representative. If you
would like more information about Visualizer products, please use the form at
the back of this book.


ΓòÉΓòÉΓòÉ 6.1. The Visualizer suite of products ΓòÉΓòÉΓòÉ

Improve the way you see your data even more with the Visualizer suite of
products. All of these products are designed to work together on your OS/2
desktop. The publications listed on the back cover of this book provide more
information about each product.

Visualizer Charts for OS/2
Present and interpret your business data visually with a variety of charting
and presentation options. Use Charts for OS/2 to graphically display trends,
compare figures, and highlight information.

Visualizer Development for OS/2
Development for OS/2 provides a comprehensive set of programming tools
specifically designed to help you develop decision-support applications. Use
Development's four components-the Menu editor, Window editor, Program editor,
and the Make facility-to design, prototype, and build applications that meet
your company's needs.

Visualizer Plans for OS/2
Project, analyze, measure, and control many aspects of your business with Plans
for OS/2, a flexible, multi-dimensional planning tool. In addition to plan
items and time, you can also define up to four dimensions for your plan to help
you solve your business and organizational problems.

Visualizer Procedures for OS/2
Automate the jobs that you do regularly by creating, maintaining, and running
programs with Procedure for OS/2. You can speed up your work by using Procedure
for OS/2 to do some tasks for you while you do others.

Visualizer Statistics for OS/2
Understand your business data using professional statistical methods provided
by Statistics for OS/2. You can choose from over 55 methods, ranging from
simple tests and regressions to model fittings and ANOVA. Statistics for OS/2
provides interfaces to help both the novice and experienced user analyze data
with minimum effort.

Visualizer Ultimedia* Query for OS/2
Store, find, hear, and see multimedia data in a relational database with
Ultimedia Query for OS/2. Store traditional data, like numbers and text, with
related images, video, audio, and documents. Sort and find images by color,
shape, texture, or layout by telling Ultimedia Query for OS/2 to "find
something that looks like this". When you find the images, just click on them
to display the image, play the video, or hear the audio recording.


ΓòÉΓòÉΓòÉ 6.2. Companion products ΓòÉΓòÉΓòÉ

Use the following products with Visualizer to open up your world of
information.

DataGuide/2
Use DataGuide/2 to manage your OS/2 objects, including Visualizer objects.
DataGuide/2 provides a single place for accessing information. You can search
for information using keywords, values, and information types, then launch
applications immediately so you can work with your data instantly.

Query Manager (QM) and Query Management Facility (QMF)
Integrate the versatility of Visualizer with the reliability you've come to
trust in solutions like QM and QMF. Create Visualizer queries, SQL Statements,
and reports from QM queries and forms, or bring your existing QMF data to the
desktop by turning QMF forms into Visualizer reports.


ΓòÉΓòÉΓòÉ 6.3. Visualizer education ΓòÉΓòÉΓòÉ

Some of the examples used in this book were taken from Visualizer educational
material.

If you would like details about Visualizer Education, please use the form at
the back of this book.


ΓòÉΓòÉΓòÉ 7. Looking at and creating SQL tables ΓòÉΓòÉΓòÉ

Use an SQL Table object to organize, access, and maintain data stored in
databases. You can use SQL tables to display and work with data that you
already use in your daily activities, and to create new tables in a database,
without using SQL at all.

Use the Visualizer Table object to work with data when you are not connected to
a database, or when you are using QUERY FOR OS/2 on a laptop computer.

You can drag and drop tables onto the following objects:

o SQL Database
o Visualizer query
o Visualizer report
o Printer
o Shredder

You can open a table to browse and update data. You can also change the table's
definition by adding columns and by updating the comments that describe the
table and its columns.

The definition of an SQL table consists of the alias of the database that the
table is stored in, a description of the table, and the attributes of each
column in the table. Column attributes are name, description, data type, size,
and position.

This chapter shows you how to look at and work with data, how to add columns,
and how to create new SQL tables.


ΓòÉΓòÉΓòÉ 7.1. Viewing and updating data in an SQL table ΓòÉΓòÉΓòÉ

When you catalog an existing database, the tables in that database are
available for you to work with. You might need to update, add, or delete
information in a table.

To display data in a table, open the table you want to work with.

The data is displayed in rows and columns, or as a table view This is the
default.

The table is in browse mode, which means that you cannot change the data or add
new data to the table.

The information area at the bottom of the window displays certain information
about the table:

o The number of rows retrieved (fetched) from the database.

o The maximum number of rows that can be retrieved from the database (the fetch
  limit).

The fetch limit is one of a number of settings that affect how you access and
view data. You can define these settings in the Profile notebook. They include:

o The fetch limit

o The time limit before the displayed data is rolled back to its last saved
  state

o The type of locking used when an SQL table is displayed

o Whether you have exclusive access to data when you display it or if another
  user can also access the data

See Customizing Query for OS/2 objects for more information on these settings.

You can also browse a table as a form by selecting from the tool bar. A form
shows one row of data at a time.

To browse each row of the table, use the Next and Previous push buttons at the
bottom of the window.

Switch back to the table view by selecting from the tool bar.


ΓòÉΓòÉΓòÉ 7.1.1. Browsing data ΓòÉΓòÉΓòÉ

When you browse data in the table view, you can:

o Change the display width of the columns
o Anchor columns so they remain on the screen when you scroll
o Display a subset of the data
o Select the columns and rows that are displayed
o Display rows of data in a specified order


ΓòÉΓòÉΓòÉ 7.1.1.1. Changing the display width of a column ΓòÉΓòÉΓòÉ

When you display data in a table, the columns are shown at a default width. The
actual length of the data in the column might be less or greater than this
width. You can change the width of the columns displayed on the screen by
dragging a column separator to the left or right. A column separator is a line
shown between the columns in the table view.

Moving the separator only changes the number of characters that are displayed,
not the number of characters the column can contain. For example, you can drag
a column separator so that only one character in the column is displayed,
although the column can contain up to thirty characters.


ΓòÉΓòÉΓòÉ 7.1.1.2. Anchoring columns ΓòÉΓòÉΓòÉ

When there are many columns in a table, you can lose track of the relationship
between the columns as you scroll across a table. To provide a point of
reference, you can anchor columns on the lefthand side of the table. These
columns stay in place as you scroll across the data.

To anchor columns:

 1. Select from the tool bar. The Fix columns window is displayed.

 2. Select the columns you want to anchor from the Columns list.

 3. Select OK.


ΓòÉΓòÉΓòÉ 7.1.1.3. Displaying a subset of columns in a table ΓòÉΓòÉΓòÉ

After you display a table, you might want to work with only a few columns at a
time. You can do this by displaying a subset of the columns in the table. The
columns that are not included in the subset are "hidden", but they are still
available.

To select a subset of columns:

 1. From the View menu, select Subset columns. The Subset columns window is
    displayed, listing the columns in the table.

 2. Select the columns you want to include in the subset.

 3. Select OK.

The columns you selected are now displayed. To change the subset, return to the
Subset columns window, select or deselect columns, then select OK. To redisplay
all columns, return to the Subset columns window, select Deselect all, then
select OK.


ΓòÉΓòÉΓòÉ 7.1.1.4. Selecting columns and rows for display ΓòÉΓòÉΓòÉ

When you display a table, all the columns in it are displayed. To work with a
specific, more manageable amount of data, you can select just some of the
columns in the table from the database, or just some of the rows.

Each time you select columns or rows, the database is reaccessed, and a new set
of data is retrieved.

To select columns:

 1. Select from the tool bar. The Select Columns window is displayed, listing
    all the columns in the table. Because the entire table is displayed
    currently, every column in the Select Columns window is selected.

 2. Deselect the columns you do not want to display, leaving those columns you
    want displayed selected. The number to the left of each column name
    indicates the position of the column when it is displayed.

 3. Select OK to confirm your column selection. The new set of data is
    retrieved from the database and displayed.

To select rows:

 1. Select from the tool bar. The Select rows window is displayed.

 2. Enter an expression that specifies the rows you want to display. For
    example, to display rows only for those employees who earn over $27,000 a
    year, type SALARY>27000

    If you're not sure how to enter the expression, select for help in building
    the expression. See Building an expression for more information on building
    expressions.

 3. Select OK. The rows that match your selection criteria are retrieved from
    the database and displayed.


ΓòÉΓòÉΓòÉ 7.1.1.5. Ordering rows within a table ΓòÉΓòÉΓòÉ

You might want to organize data in a column in a particular order. For example,
you might want to display the employees in your department by salary (highest
to lowest). To do this, you can order the rows within a table:

 1. Select from the tool bar. The Order rows window is displayed.

 2. Select a column from the Available columns list, then select a sorting
    order. Select Ascending to order the rows from lowest to highest (for
    example, 1 to 9), or Descending to order the rows from highest to lowest
    (for example, 9 to 1).

 3. Select Add to add the column to the Column list. The Order column displays
     to indicate ascending order and  to indicate descending order.

 4. Repeat steps 2 and 3 to order the table even more.

 5. Select OK. The table is redisplayed using the ordering criteria you
    specified.


ΓòÉΓòÉΓòÉ 7.1.2. Updating data ΓòÉΓòÉΓòÉ

You can update data in both the form and table views. Select to switch from
browse mode to update mode.

The table must have a primary key defined before you can update it. If no
primary key is defined, the Specify key for update window is displayed when you
enter update mode so you can set a temporary key.

The temporary key is not part of the table, and is not saved when you close or
save the table.

To set a temporary key:

 1. In the Specify key for update window, select the column or columns to be
    included in the key. The column or combination of columns you select must
    uniquely identify each row in the table.

 2. Select OK.

You can now update the data and the structure of the table.

You can change the temporary key by selecting to display the Specify key for
update window and modifying the current selections.


ΓòÉΓòÉΓòÉ 7.1.2.1. Working with the table view ΓòÉΓòÉΓòÉ

When you display data as a table, you use handles to select the columns, rows,
and cells. Handles appear above columns and to the left of rows and cells.

You can select columns, rows, and cells in several ways:

o To select a single column, row, or cell, select its handle.

o To select more than one column, row, or cell, select a handle, then select
  other handles while holding the Ctrl key.

o To select a contiguous range of columns, rows, or cells, select the first
  handle in the range, then select the last handle in the range while holding
  down the Shift key.

To move between cells in a row, press the Tab key. To move to the next row,
press Enter.


ΓòÉΓòÉΓòÉ 7.1.2.2. Working with the form view ΓòÉΓòÉΓòÉ

When using the form view, you display and work with your data by using the push
buttons at the bottom of the window:

Next           Displays the next row.
Previous       Displays the previous row.
Add            Adds a new row containing the currently displayed data.
Delete         Removes the current row and its data from the table.
Clear          Inserts a new, blank row into the table.
Reset          Restores any changes you made to the current row back to their
               original values.


ΓòÉΓòÉΓòÉ 7.1.2.3. Adding rows and data to a table ΓòÉΓòÉΓòÉ

If you are working with the table view, you can add a new row by selecting from
the tool bar. Then, you can enter new data into the row.

If you are working with the form view, you can add a new row in two ways.

o To add a new row that contains data similar to a row already in the table,
  change the data that is different and select Add. A new row with the updated
  data is added to the table.

o To add a new, blank row to the table, select Clear. Type the new data into
  the blank row, and select Add.

To move between cells, press the Tab key.


ΓòÉΓòÉΓòÉ 7.1.2.4. Deleting rows ΓòÉΓòÉΓòÉ

You can delete rows that are no longer needed in a table. In the table view,
select the row or rows you want to delete, then select Delete from the Edit
menu. In the form view, select the Delete button to delete the currently
displayed row.


ΓòÉΓòÉΓòÉ 7.2. Changing an existing SQL table ΓòÉΓòÉΓòÉ

You might want to change an existing SQL table by adding columns or by
modifying the comments that describe an SQL table and its columns.


ΓòÉΓòÉΓòÉ 7.2.1. Adding columns to an SQL table ΓòÉΓòÉΓòÉ

 1. Open the notebook.

 2. On the Columns page, type the name of the column in the Name field.

 3. Type a brief comment describing the column in the Comment field.

 4. Select a data type from the Data Type list.

 5. Depending on data type selected, use the Precision and Scale fields or the
    Width field to specify the maximum number of characters the column can
    contain.

 6. Select a button under Allow Nulls? to indicate if null values (blanks in
    the databases) can be included in the column. In some databases, you can
    use the default for the specified data type.

 7. Select Add. The information you just entered is shown in the Column List.
    The column name is enclosed in brackets, indicating that the column has not
    been applied to the database. When the column is applied, the brackets are
    removed.

 8. Select Clear to clear the entry fields.

 9. Repeat steps 2 through 8 for each column in the SQL table.

10. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.


ΓòÉΓòÉΓòÉ 7.2.2. Modifying comments for an SQL table and its columns ΓòÉΓòÉΓòÉ

You can update or delete comments from an SQL table that exists in a database.

To update a comment for an SQL table:

 1. Open the notebook. On the Table page, the current comment for the SQL table
    (if any) is displayed in the Comment field.

 2. Update or delete the comment in the Comment field.

   o Edit the comment or, if no comment exists, type a new one.
   o Highlight the comment and press the Delete key on your keyboard.

 3. Select Apply all to apply the new comment to the database, and close the
    notebook.

To update a comment for a column:

 1. Open the notebook.

 2. On the Columns page select, from the Column List, the column name that
    contains the comment you want to update. The information for the selected
    column is displayed in the fields in the Column name and definition area.

 3. Update or delete the comment displayed in the Comment field:

   o Edit the current comment or type a new one.
   o Highlight the comment and press the Delete key on your keyboard.

 4. Select Modify.

 5. Repeat steps 2, 3, and 4 for each comment you want to update.

 6. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.


ΓòÉΓòÉΓòÉ 7.3. Creating new SQL tables ΓòÉΓòÉΓòÉ

You can create new SQL tables from existing SQL tables, from an SQL table
designated as a template, or from the SQL Table template object in the
Visualizer folder. After you create the new table, you can display it and add
data to it. See Viewing and updating data in an SQL table for information on
adding data to an SQL table.


ΓòÉΓòÉΓòÉ 7.3.1. Creating a new SQL table based on an existing SQL table ΓòÉΓòÉΓòÉ

You might want to create a new SQL table with the same column attributes as an
SQL table that already exists in an SQL database, but without the same data.
You might do this for tables that contain the same type of information (for
example, product names, product numbers, quantity sold, and cost for a
particular month), but not the same data.

If you need to do this only once, you can create a new SQL table with the same
definition as an existing SQL table by using the Create from menu choice.

If you want to create several SQL tables based on a single definition, you can
create a template with a specific definition. See Common OS/2 tasks for
information on how to do this.

Before you apply a new SQL table to the database, you can change any
information in the SQL table. After you apply the SQL table to a database, you
can modify only the comments for the SQL table and for the columns in the SQL
table. See Modifying comments for an SQL table and its columns for more
information.

To create a new SQL table from an existing SQL table definition:

 1. Locate the existing SQL table you want to use.
 2. Ensure that all changes to the existing SQL table are applied to the
    database.
 3. Display the pop-up menu for the SQL table.
 4. Select Create from. An icon for the new SQL table is displayed.
 5. Open the notebook for the new SQL table.
 6. Modify the information for the SQL table or its columns. This step is
    optional.
 7. Select Apply all on the Table page and close the notebook.

The new SQL table is created in the same SQL database as the existing SQL
table, but has a slightly different name than the original.


ΓòÉΓòÉΓòÉ 7.3.2. Creating and defining a new SQL table ΓòÉΓòÉΓòÉ

You might want to create a new SQL table with different characteristics than
any existing SQL table. You can do this by using the SQL Table template in the
Shadows of Templates folder.

To create a new SQL table:

 1. Create a new SQL table. See Common OS/2 tasks for information on how to do
    this. An icon for the new SQL table is displayed.

 2. Open the notebook for the new SQL table.

 3. On the Table page, type the name of the SQL table in Name.

 4. Type a comment describing the SQL table in the Comment field.

 5. Add columns to the SQL table as described in Adding columns to an SQL
    table.

 6. Select Apply all on the Table page and close the notebook.


ΓòÉΓòÉΓòÉ 7.4. Exporting SQL tables ΓòÉΓòÉΓòÉ

You can export a table from a database to a Visualizer table, or to the
following file types:

o IXF (PCIXF)
o DEL
o WSF

When you export a table, the structure and contents of the current table are
copied to the destination or file type you specify. The current table is
unchanged.

When you export an SQL table to a Visualizer table, the data is stored
differently. Installing and Supporting Visualizer Query for OS/2 describes the
differences.

To export a table:

 1. Display the table you want to export.

 2. Select Export from the Table menu.

 3. In the Export as field, select the type of file to be exported.

 4. Type the name of the exported table in the Name field.

 5. If necessary, type the location of the exported table in the Location
    field.

 6. Select Export.


ΓòÉΓòÉΓòÉ 8. Looking at and creating SQL views ΓòÉΓòÉΓòÉ

Use an SQL View object to access views in your SQL databases, and to create new
views. A view contains a subset of information from selected SQL tables. You
can drag and drop views onto the following objects:

o SQL Database
o Visualizer query
o Visualizer report
o Printer
o Shredder

You can open a view to browse and update data. You can also copy the definition
of an existing view to a new view, and you can define a new view by using a
template.

The definition of a view consists of the statement used to create the view, a
comment describing the view and, if necessary, aliases for the columns in the
view.

This chapter shows you how to look at data in a view, create new views, and
create aliases for columns in a view.


ΓòÉΓòÉΓòÉ 8.1. Looking at and changing data in a view ΓòÉΓòÉΓòÉ

When you catalog an existing database, the views in that database are available
for you to work with. You might need to update information in a view, add
information to a view, or remove information from a view.

You can change or add information to an updateable view, but you can only
browse data in a read-only view.

To display your data, open the view you want to work with. After the view is
displayed, you can work with it as described in Viewing and updating data in an
SQL table


ΓòÉΓòÉΓòÉ 8.2. Creating new views ΓòÉΓòÉΓòÉ

You can create new views from existing views, from a view designated as a
template, or from the SQL View template object in the Shadows of Templates
folder. After you create a new view, you can display it and work with it as
described in Viewing and updating data in an SQL table.


ΓòÉΓòÉΓòÉ 8.2.1. Creating a new view based on an existing view ΓòÉΓòÉΓòÉ

You can use the definition of an existing view as the basis of new views. You
might do this if you want to slightly modify the existing view definition to
create a new view.

If you need to do this only once, you can create a new view with the same
definition as an existing view by using the Create from menu choice.

If you want to create several views based on a single definition, you can
create a template with a specific definition. See Common OS/2 tasks for
information on how to do this.

Before you apply the new view to a database, you can change the view name and
comment, modify the definition statement, and, if necessary, add or change
aliases and comments for columns. After you apply the view to a database, you
can modify only the comments for the view and for the columns in the view. See
Updating comments for a view and its aliases for more information.

To create a new view from an existing view definition:

 1. Locate the existing view you want to use.

 2. Ensure that all changes to the existing view are applied to the database.

 3. Display the pop-up menu for the view.

 4. Select Create from. An icon for the new view is displayed.

 5. Open the notebook for the new view.

 6. Modify the information for the view or its columns. This step is optional.

 7. Select Apply all on the View page of the notebook.

 8. Close the notebook.

The new view is created in the same folder object as the existing view, but has
a slightly different name than the original.


ΓòÉΓòÉΓòÉ 8.2.2. Creating and defining a new view ΓòÉΓòÉΓòÉ

You might want to create a new view with different characteristics than any
existing view. You can do this by using the SQL View template in the Shadows of
Templates folder.

To create a new view:

 1. Create a new view. See Common OS/2 tasks for information on how to do this.
    An icon for the new view is displayed.

 2. Open the notebook for the new view.

 3. On the View page, type a name for the view in the View field.

 4. Type a comment describing the view in the Comment field.

 5. On the Definition page, enter the view definition statement in the SQL
    Definition field.

   o If you are not familiar with SQL, you can create a view definition
     statement in the following way:

       a) Use a Visualizer query to select the data you want to include in the
          view.

       b) Select from the tool bar to display the underlying SQL.

       c) Copy the SQL and paste it in the SQL definition field.

     See Producing SQL using a query for more information on how to do this.

   o If you are familiar with SQL, type the SELECT portion of the CREATE VIEW
     statement into the SQL Definition field.

 6. If necessary, create aliases for each column in the view. See Adding
    aliases and updating comments for more information.

 7. Select Apply all on the View page of the notebook.

 8. Close the notebook.


ΓòÉΓòÉΓòÉ 8.3. Adding aliases and updating comments ΓòÉΓòÉΓòÉ

Before you apply a view to a database, you can modify it by adding aliases.
After you apply a view to a database, you can modify only the comments for the
view and for any aliases in the view.


ΓòÉΓòÉΓòÉ 8.3.1. Adding aliases for column names to an view ΓòÉΓòÉΓòÉ

Aliases help simplify the sometimes unwieldy column names that are stored in a
database. You can use an alias to give a column a name that is easier for you
to remember than the column's original name.

Aliases are usually not required. However, if the view definition selects
columns with duplicate names, or if the view definition creates a calculated
column, you need to use aliases for the columns in the view. When using
aliases, follow these rules:

o You can only create aliases in a view that is not applied to a database.

o Create aliases in the order of the columns listed in the definition
  statement.

o If you create an aliases for one column in a view, you must create aliases
  for all the columns in the view.

o After you select Apply all, you cannot change the aliases.

To add aliases to a view:

 1. Open the notebook to the Definition page.

 2. Type a name for the alias in the Name field.

 3. Type a comment for the alias in the Comment field.

 4. Select Add.

 5. Select Clear to clear the fields.

 6. Repeat steps 2 through 5 for each column in the view.

 7. Select Apply all on the View page of the notebook.

 8. Close the notebook.


ΓòÉΓòÉΓòÉ 8.3.2. Updating comments for a view and its aliases ΓòÉΓòÉΓòÉ

After a view is applied to a database, you can update or remove comments for
the view and for any aliases in the view.

To update a comment for a view:

 1. Open the notebook. On the View page, the current comment for the view (if
    any) is displayed in the Comment field.

 2. Update or remove the comment in the Comment field.

   o Edit the comment or, if no comment exists, type a new one.
   o Highlight the comment and press the Delete key on your keyboard.

 3. Select Apply all to apply the new comment to the database.

 4. Close the notebook.

To update comments for aliases:

 1. Open the notebook.

 2. On the Definition page, select an alias name from the list displayed in the
    bottom half of the notebook. The name and comment for the selected alias
    are displayed in the Name and Comment fields.

 3. Update or delete the comment displayed in the Comment field:

   o Edit the comment or, if no comment exists, type a new one.
   o Highlight the comment and press the Delete key on your keyboard.

 4. Select Modify.

 5. Repeat steps 2, 3, and 4 for each comment you want to update.

 6. Select Apply all on the View page of the notebook to apply the changes to
    the database.

 7. Close the notebook.


ΓòÉΓòÉΓòÉ 9. Selecting and combining data using queries ΓòÉΓòÉΓòÉ

Use a Visualizer Query object to select, combine, and analyze information
stored in an SQL database, or in files using a number of common data formats.
Visualizer Query enables you to select just the information you need from your
data without requiring an understanding of SQL. For example, you could select
information on product stock levels in your warehouses, combine this with data
on unfulfilled orders from stores, and find out if you could satisfy more
orders by moving stock between warehouses.

You can drag and drop a Visualizer Query onto the following objects:

o Visualizer report
o Folder
o Out-basket (electronic mail)
o Shredder

This chapter describes, for those who use predefined queries, how to use a
query to provide data for a report, and how to run a query and view the result.
It also describes, for those defining their own queries, how to combine and
select data from two SQL tables or views, and how to save a query.


ΓòÉΓòÉΓòÉ 9.1. Providing data using a query ΓòÉΓòÉΓòÉ

If you do not define queries yourself, you can think of a query as being just
like a data table. Like a table, it supplies the data you want in a row and
column format. You can look at the data, and you can use it to create a report.
Unlike SQL tables and SQL views, queries are not stored in a database object.
You can store them in any OS/2 folder, or on the desktop.

To provide data for a report using a query, drag the query and drop it on an
open report. You can drag either the icon of a closed query, or the small icon
of an open query.


ΓòÉΓòÉΓòÉ 9.2. Viewing the result of a query ΓòÉΓòÉΓòÉ

The data that a query provides is called the result of the query. This data is
not stored in the query, but is retrieved from other data sources when you run
the query. You must run a query before you can look at its result.

To run a query without opening it:

 1. Display the pop-up menu for the query.
 2. Select the arrow to the right of Open.
 3. Select Run query.

If Run query has a check mark next to it, it is the default selection on the
cascaded menu. In this case, you can run the query by double-clicking on its
icon. For information on how to change the default selection on the cascaded
menu, see Retrieving and manipulating data using queries.

To run an open query, select from the tool bar.

The result of the query is shown in a browsing window. You can look at the data
in this window, but you cannot update it. You can see the result either in row
and column format, called the table view, or in index card format, called the
form view.

Use the table view to show information about lots of items at once. Choose the
table view by selecting from the tool bar.

Use the form view to show information about a single item at a time. Choose the
form view by selecting from the tool bar.

You can save the result as a table or in a number of other data formats in the
OS/2 file system. For more information about using the data browsing window,
see Viewing and updating data in an SQL table.


ΓòÉΓòÉΓòÉ 9.3. Defining a simple query ΓòÉΓòÉΓòÉ

Sometimes the data you want to see is held in more than one table or view.
Sometimes you may not want to see a whole table, but only information about
specific items in the table. Use a query to combine data from several related
tables or views and to select just the data you want.

Defining a query to combine data in two tables or views is very easy, and is
described in detail in the following sections. The basic steps are:

 1. Create a new query and rename it. See Common OS/2 tasks for information on
    how to do this.

 2. Open the query.

 3. Select the tables and views you need. For information on how to do this,
    see Selecting data sources for your query.

 4. Link the data sources. QUERY FOR OS/2 can do this for you if you choose.
    For further information, see Combining data sources.

When you have combined the data in the two tables or views, you can choose to
see data about specific items, rather than looking at all the combined data.
For example, if your combined data is about products you sell, you might want
to look at only the data for one or two specific products. For information on
how to do this, see Selecting rows of data.

You can also choose not to see all the available information about the specific
items you have selected. For example, your combined product data might contain
data about how each product is packaged. You can choose to see all the data
about the products you have selected except the packaging data. For information
on how to do this, see Selecting columns of data.


ΓòÉΓòÉΓòÉ 9.4. Selecting data sources for your query ΓòÉΓòÉΓòÉ

You can select any of the following data sources to use in a query:

o SQL tables
o SQL views
o Queries
o Visualizer tables

All SQL tables and SQL views in a query must be associated with the same
database object. You cannot mix Visualizer tables or external data formats with
SQL tables or SQL views within a single query.


ΓòÉΓòÉΓòÉ 9.4.1. Selecting data source icons ΓòÉΓòÉΓòÉ

Usually, you can see the icons of the data sources you want. For example, they
might be in a database object, or on the desktop.

To select data sources for your query when you can see their icons, drag the
icons one by one, and drop them on an open query.


ΓòÉΓòÉΓòÉ 9.4.2. Selecting data sources without icons ΓòÉΓòÉΓòÉ

Sometimes you may not be able to see the icon of a data source you want. It
might not exist on your workstation, or you might not know where it is.

If you cannot find an icon you want, select from the tool bar. The Select
Tables and Queries and the Selected Tables and Queries windows are displayed.

Use the Select Tables and Queries window to find data sources in the OS/2 file
system, and other data sources cataloged on your workstation. For more
information on finding data sources that are not stored in an SQL database, see
the online help for the Select Tables and Queries window.

To select tables and views from SQL databases:

 1. Ensure that SQL is selected in the Type list. (SQL is the default
    selection).

    A list of all the SQL databases that you have cataloged on your workstation
    is shown in the Locations list.

 2. Select a database in the Locations list.

    A list of all collection names associated with the selected database is
    shown in the Locations list. The collection name (sometimes called the
    table prefix) is usually the same as the user ID of the person who created
    the tables and views.

 3. Select a collection name in the Locations list.

    A list of tables and views identified by the selected collection name is
    shown in the Names list.

 4. Press and hold down the Ctrl key, and select the tables and views you want.

    As you select tables and views, their names are displayed in the Selected
    Tables and Queries window.

 5. In the Select Tables and Queries window, select OK.


ΓòÉΓòÉΓòÉ 9.5. Combining data sources ΓòÉΓòÉΓòÉ

For each data source you have selected, an entry appears in the Data sources
list in the query main window. At the top of the entry is the name of the data
source. Below this, the columns available in the data source are listed.

To combine related data from more than one data source, the data sources must
be linked. (You do not need to understand linking to define a simple query, but
if you are interested in knowing more, see Understanding links.) You can
identify a linked column by the symbol () that appears next to it in a link
entry. A link entry is an area in the data sources list between each data
source and the next one. You can recognize a link entry by the symbols > < that
appear at the top of the entry.

Depending on the settings of the data sources you are using, there may already
be link symbols () in some or all of the link entries. A link symbol appears
next to a column that has been linked to a column in another data source.
Visualizer Query automatically created these links when you selected the data
sources. If all the link entries contain at least one link symbol (), you do
not need to take any further action. The data sources are ready to be combined.

If any of the link entries are empty, create default links for that entry:

 1. Double-click on the link entry. The Link Settings window is displayed.

 2. Select Auto join.

    The names of the columns that have been linked are shown in the Linked
    columns list.

 3. Select OK.

When all the link entries in your query contain link symbols, your data sources
are ready to be combined.


ΓòÉΓòÉΓòÉ 9.5.1. Understanding links ΓòÉΓòÉΓòÉ

When you link data sources, you:

o Specify which columns in one data source relate to columns in other data
  sources. Only some of the columns in a data source have any relationship with
  columns in other data sources.

o Specify how the tables should be combined. Do this by specifying the type of
  link to be used. To combine columns from several different, but related, data
  sources so that the data appears to be in one larger data source, use the
  join link type.

For example, in the query shown in Combining data sources, the tables ORDERS
and PART_NUM are related. They are related because they both contain a column
containing the same information, PARTNO.

The person who created the two tables included this column in both tables so
that they could be cross-referenced. Any specific row in the ORDERS table has a
part number in the PARTNO column. To find out what the part is, you have to
look in the PART_NUM table and find the row corresponding to this part number.
The part is described in the PART and PARTNAME columns of the PART_NUM table.

When you run this query, it combines the two tables using a join. For each row
in the ORDERS table, it looks up the relevant row in the PART_NUM table, and
appends this to the end of the row in the ORDERS table. The PARTNO column from
the second table is not shown in the result, as it contains the same
information as the one in the first table.

This operation is shown in the following diagram:

ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé PARTNO Γöé DEPOT   Γöé QUANTITY Γöé  Γöé PARTNO Γöé PART  Γöé PARTNAME    Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 300   Γöé New York Γöé 12    Γöé  Γöé 300   Γöé Bolt  Γöé 4inch steel  Γöé
Γöé 500   Γöé New York Γöé 200    Γöé  Γöé 500   Γöé Nut   Γöé 1inch steel  Γöé
Γöé 300   Γöé Montreal Γöé 74    Γöé  Γöé 700   Γöé Washer Γöé M5 shakeproof Γöé
Γöé 300   Γöé New York Γöé 43    Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé 500   Γöé Montreal Γöé 32    Γöé   PART_NUM
Γöé 500   Γöé Montreal Γöé 82    Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
 ORDERS

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

    ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
    Γöé PARTNO Γöé DEPOT   Γöé QUANTITY Γöé PART  Γöé PARTNAME   Γöé
    Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
    Γöé 300   Γöé New York Γöé 12    Γöé Bolt  Γöé 4inch steel Γöé
    Γöé 500   Γöé New York Γöé 200    Γöé Nut  Γöé 1inch steel Γöé
    Γöé 300   Γöé Montreal Γöé 74    Γöé Bolt  Γöé 4inch steel Γöé
    Γöé 300   Γöé New York Γöé 43    Γöé Bolt  Γöé 4inch steel Γöé
    Γöé 500   Γöé Montreal Γöé 32    Γöé Nut  Γöé 1inch steel Γöé
    Γöé 500   Γöé Montreal Γöé 82    Γöé Nut  Γöé 1inch steel Γöé
    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
     Result

This explanation of a join has been slightly simplified. For more information
about joins, and all the other kinds of links supported by query, see Choosing
a link type.


ΓòÉΓòÉΓòÉ 9.6. Selecting rows of data ΓòÉΓòÉΓòÉ

Use query to select rows of data that fulfill a specified condition. You can
select rows from either a single data source, or from the result of linking
several data sources. You might want to select rows for any of these reasons:

o You are only interested in certain items in the data, and don't want to look
  at all the items. For example, you have data about lots of products, but are
  only interested in those sold under a specific brand name.

o You want to highlight items that need your attention. For example, it would
  be useful to know which of your account customers have not paid their bills
  for more than 3 months.

o You want to look at data that has so many rows that it would need more memory
  than you have in your workstation. For example, you have a huge mainframe
  database of daily sales figures for stores in major cities across the
  country. You can look at the data for each city in turn, or for each separate
  product line countrywide.

To select rows:

 1. Select from the tool bar. The Select Rows window is displayed.

 2. Type an expression that specifies the rows you want.

    For example, the expression shown specifies that only rows where the depot
    is New York or Montreal should be selected.

    Type your expression using the syntax indicated in brackets above the
    Expression field. If you aren't familiar with the syntax indicated, see
    Point and click expression entry.

 3. In the Select Rows window, select OK.


ΓòÉΓòÉΓòÉ 9.6.1. Point and click expression entry ΓòÉΓòÉΓòÉ

Use the Expression Builder window to help you type expressions in the correct
syntax even when you are not familiar with the syntax. Whenever you see in a
window, the Expression Builder window is available to help you.

To create the expression shown in Selecting rows of data

 1. Select to display the Expression Builder window.

 2. Select DEPOT from the column names in the Operands and operators list on
    the left of the window.

 3. Select Comparison operators from the Operand and operator types list on the
    right of the window.

 4. Select = from the refreshed list on the left of the window.

 5. Type `New York` in the Expression field.

    In SQL syntax, when you want to look for specific words in a column,
    enclose them in single quotes. If you are looking for specific numeric
    values in a column, the quotes are not required.

 6. Select Logic symbols from the list on the right.

 7. Select OR from the list on the left.

 8. Repeat steps 2 through 4

 9. Type in the Expression field.

10. Select OK.

Your expression is checked to ensure the syntax is correct, and the Expression
Builder window closes. The expression you have built is shown in the window in
which you selected .


ΓòÉΓòÉΓòÉ 9.7. Selecting columns of data ΓòÉΓòÉΓòÉ

Use query to select specific columns of data. You can select columns from
either a single data source, or from the result of linking several data
sources. You might want to select columns for these reasons:

o You are only interested in some of the information that is held about each
  item in the data source. For example, you have data about your suppliers. You
  are interested in their contact names and phone numbers, but not their
  addresses.

o You want to look at data from a large data source that would need more memory
  than you have in your workstation. For example, you have a mainframe database
  of credit card transactions across the country. You are only interested in
  the amount spent in each city, and not in the merchant's name or reference
  number.

To select columns:

 1. Select from the tool bar. The Select Columns window is displayed.

    By default, all available columns are selected.

 2. Deselect the columns you do not want. If you only want a few columns from a
    list that contains many, select the Deselect all push button and then
    select the columns you want.

 3. Select OK.


ΓòÉΓòÉΓòÉ 9.8. Saving a query ΓòÉΓòÉΓòÉ

To save a query, select Save from the Query menu. The query is saved in the
OS/2 file system.

You cannot select Save if you do not have write access to the query that you
have opened. However, you can save it using a different name or location.

To save a query using a different name or location:

 1. Select Copy to from the Query menu.
 2. In the Copy To window, type a file name for the query in Name.
 3. Type a location for the query in Location.
 4. Select OK.


ΓòÉΓòÉΓòÉ 10. Retrieving and manipulating data using queries ΓòÉΓòÉΓòÉ

Use a Visualizer Query object to retrieve and manipulate data from one or more
data sources. You can select and combine data from one data source using
another, calculate data, group data and summarize data. This chapter covers the
following topics:

o Creating different types of joins
o Embedding one query within another
o Using advanced options
o Reusing queries by including variables
o Creating queries to be used by others


ΓòÉΓòÉΓòÉ 10.1. Defining a query ΓòÉΓòÉΓòÉ

The previous chapter described how to define a simple query using a single data
source, or two data sources linked with a join, and row and column selections.
However, you can use the powerful features of Visualizer Query to define
considerably more complex queries when you need them to retrieve the result you
want. The data shown in the result of a query is determined by the following:

o The data sources you select, and the order in which you select them.

  For more information, see:

   - Selecting data sources
   - Mixing different types of data in a query

o The types of links you specify when linking data sources.

  For more information, see:

   - Linking data sources
   - Choosing a link type

o The expressions you use to select, calculate and summarize data.

  For more information, see:

   - Selecting, calculating and summarizing data
   - Selecting data from a partial query


ΓòÉΓòÉΓòÉ 10.2. Selecting data sources ΓòÉΓòÉΓòÉ

When you define a query, the order in which you select the data sources affects
the query result in two ways:

o By determining the columns available for linking when each data source is
  added to the query. This applies when using any link type.

o By determining the data source that data will be retrieved from. This applies
  when using a semi join, an except join or an outer join.


ΓòÉΓòÉΓòÉ 10.2.1. Making columns available for linking ΓòÉΓòÉΓòÉ

To make columns in a data source available for linking to more than one other
data source, select it before the tables you want to link it to.

For example, suppose you want to define a query to tell you which payment
method your customers prefer, state by state. You have three data sources:

o A table called PAYMENT that tells you the payment method used for each order
  number.

o A table called CUSTOMER that tells you which customer placed a given order
  number.

o A table called STATE, that tells you the name of the state in which each
  customer is based.

You can define the query you want by linking the three tables with a join,
grouping the result by state name, and counting the number of times each
payment method was used.

You need to link both the PAYMENT and STATE tables to the CUSTOMER table, so
you must select the CUSTOMER table first. Its columns are then available for
linking to either of the other tables, which can be selected in any order.


ΓòÉΓòÉΓòÉ 10.2.2. Controlling data retrieval ΓòÉΓòÉΓòÉ

You must also consider the type of links you want to use in your query when
deciding on the order in which to select your data sources. A join produces the
same result regardless of the order of the data sources it links. However, some
link types produce different results depending on the order in which you
specified the data sources they link:

o Semi joins retrieve data from only the first data source
o Except joins retrieve data from only the first data source
o Outer joins replace non-matching data from the second data source with nulls


ΓòÉΓòÉΓòÉ 10.3. Mixing different types of data in a query ΓòÉΓòÉΓòÉ

You can combine data stored in an SQL database with non-SQL database data by
using an embedded query. An embedded query is a query that is used as a data
source within another query. You can link columns in the result of an embedded
query to other, non-SQL database data sources to define a new query.

To combine data from an SQL database with other data:

 1. Create a query that produces the result you want from the SQL database data
    sources.

 2. Create another query that uses the first query as a data source and
    combines its result with the non-SQL database data sources.

Only a query that links non-SQL database data can contain an embedded query.

For example, suppose you need to combine data stored in different SQL
databases. You might at first think you could do this by embedding a query that
accesses one database in a query that accesses the other database. However, the
result of a query is a non-SQL database data source, so it cannot be used in a
query that uses SQL database data.

To achieve the result you want, you need to create a query containing two
embedded queries. The embedded queries access data in each of the two SQL
databases, and the main query links their results.


ΓòÉΓòÉΓòÉ 10.4. Linking data sources ΓòÉΓòÉΓòÉ

After you add a data source to a query, you can link its columns to columns in
any of the data sources you previously selected.
To link columns in a data source:

To link columns in a data source:

 1. Double-click on the link entry to the left of the data source in the Data
    sources list. You can identify a link entry by the symbols > < that appear
    at the top of the entry.

    The Link Settings window is displayed.

 2. Select the type of link you want from the Link type list. For more
    information on the different types of link available, see Choosing a link
    type.

 3. Link the columns automatically or manually:

   o To link columns automatically, select Auto join. Columns in the current
     data source with the same name and data type as columns in the previous
     data source are linked using the link type you specified.

   o To link columns manually:

       a) Select the columns you want to link from the lists in Link columns.
          The Available columns list shows all columns available for linking
          from any of the previously selected data sources.
       b) Select the Link push button.

          The columns you linked appear in the Linked columns list.

 4. Select OK.


ΓòÉΓòÉΓòÉ 10.5. Choosing a link type ΓòÉΓòÉΓòÉ

You can use the line types shown below in a Visualizer query. Each link type is
described in detail.

Join

o Combines data sources that have a common linking column
o Columns from both data sources are included in the result
o Combines all rows that contain identical values in the linking column
o Can use SQL database data source
o Can use Query, Visualizer table, and external sources

Semi Join

o Selects data in one data source using data from another
o Includes all rows containing identical values in the linking columns
o Only columns from the first data source are included in the result
o Can use SQL database data source
o Can use Query, Visualizer table, and external sources

Except

o Excludes data in one data source using data from another
o Includes only rows not containing identical values in the linked column
o Only columns from the first data source are included in the result
o Can use SQL database data source
o Can use Query, Visualizer table, and external sources

Union

o Combines data in SQL database data sources with the same structure
o If a row is unique, it is included
o If an entire row is identical in both data sources, it is included once in
  the result
o If a row is not identical in both data sources, both rows are included
o If necessary, a new key column is created in the result
o Can use SQL database data source

Unite

o Combines data in non-SQL database data sources with the same structure
o A row is included in the result only if it has a unique key
o Non-key columns are not compared
o If the linked key columns contain identical values, the row from the second
  data source is included
o If the linked key columns do not contain identical values, the rows from both
  data sources are included
o Can use Query, Visualizer table, and external sources

Outer join

o Can be used to check the referential integrity of your data
o Includes all rows from the first table in the result
o Includes columns from both tables
o If there is no corresponding row in the second table, null values are shown
  for the columns from the second table
o Can use Query, Visualizer table, and external sources


ΓòÉΓòÉΓòÉ 10.5.1. Join ΓòÉΓòÉΓòÉ

Use Join to define a natural join between two data sources that includes, from
both data sources, all rows containing identical values in the linking columns
common to each. All the linked columns from the first data source are shown in
the result, together with all unlinked columns from both data sources.

In the example below, the linking column is A and matching rows are in red.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé D  Γöé E    Γöé F    Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 30 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
Γöé 20 Γöé Jones Γöé 46324 Γöé  Γöé 50 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
Γöé 30 Γöé Brown Γöé 96232 Γöé  Γöé 70 Γöé AAA Γöé 621.70 Γöé 634.58 Γöé
Γöé 40 Γöé Call  Γöé 72355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé 50 Γöé Davis Γöé 32553 Γöé   Second data source
Γöé 60 Γöé Reed  Γöé 82375 Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

   ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
   Γöé A  Γöé B   Γöé C   Γöé D  Γöé E    Γöé F    Γöé
   Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
   Γöé 30 Γöé Brown Γöé 96232 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
   Γöé 50 Γöé Davis Γöé 32553 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
   ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
     Result


ΓòÉΓòÉΓòÉ 10.5.2. Semi Join ΓòÉΓòÉΓòÉ

Use Semi Join to define a join between two data sources that includes, from
both data sources, all rows containing identical values in the linking column
common to each, but only uses columns from the first data sources selected for
the join.

In the example below, the linking column is A, and matching rows are in a red.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé D  Γöé E    Γöé F    Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 30 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
Γöé 20 Γöé Jones Γöé 46324 Γöé  Γöé 50 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
Γöé 30 Γöé Brown Γöé 96232 Γöé  Γöé 70 Γöé AAA Γöé 621.70 Γöé 634.58 Γöé
Γöé 40 Γöé Call  Γöé 72355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé 50 Γöé Davis Γöé 32553 Γöé   Second data source
Γöé 60 Γöé Reed  Γöé 82375 Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

         ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
         Γöé A  Γöé B   Γöé C   Γöé
         Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
         Γöé 30 Γöé Brown Γöé 96232 Γöé
         Γöé 50 Γöé Davis Γöé 32553 Γöé
         ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
         Result

Use Except to get the opposite result of a semi join.


ΓòÉΓòÉΓòÉ 10.5.3. Except ΓòÉΓòÉΓòÉ

Use Except to define a join between two data sources that includes, from both
data sources, all rows not containing identical values in the linking column
common to each, but that uses only columns from the first data source selected
for the join.

In the example below, the linking column is A, and matching rows are in red.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé D  Γöé E    Γöé F    Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 30 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
Γöé 20 Γöé Jones Γöé 46324 Γöé  Γöé 50 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
Γöé 30 Γöé Brown Γöé 96232 Γöé  Γöé 70 Γöé AAA Γöé 621.70 Γöé 634.58 Γöé
Γöé 40 Γöé Call  Γöé 72355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé 50 Γöé Davis Γöé 32553 Γöé   Second data source
Γöé 60 Γöé Reed  Γöé 82375 Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

         ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
         Γöé A  Γöé B   Γöé C   Γöé
         Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
         Γöé 10 Γöé Smith Γöé 50322 Γöé
         Γöé 20 Γöé Jones Γöé 46324 Γöé
         Γöé 40 Γöé Call  Γöé 72355 Γöé
         Γöé 60 Γöé Reed  Γöé 82375 Γöé
         ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
         Result

Use Semi Join to get the opposite result of an Except join.


ΓòÉΓòÉΓòÉ 10.5.4. Union ΓòÉΓòÉΓòÉ

Use Union to define a join between two SQL data sources that includes all the
rows of the two data sources, but shows duplicate rows only once in the result.

The two data sources must have the same structure, with the same number of
columns, the same column names and the same column data types. Each pair of
columns is linked automatically. If a pair of linked columns contains different
values for a row, the rows from both data sources are shown, and a new key
column is generated if necessary.

In the example below, the linking columns are A and B, and matching rows are in
red. However, the matched rows have only partially matching values in the
columns. Magenta represents values in the first data source that are different
from the corresponding values in the second.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé B   Γöé C   Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 20 Γöé Jones Γöé 46324 Γöé
Γöé 20 Γöé Jones Γöé 74554 Γöé  Γöé 40 Γöé Call  Γöé 72355 Γöé
Γöé 30 Γöé Brown Γöé 98232 Γöé  Γöé 50 Γöé Ward  Γöé 97805 Γöé
Γöé 40 Γöé King  Γöé 26355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ   Second data source
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

     ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
     Γöé Key Γöé A  Γöé B   Γöé C   Γöé
     Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
     Γöé 1  Γöé 10 Γöé Smith Γöé 50322 Γöé
     Γöé 2  Γöé 20 Γöé Jones Γöé 74554 Γöé
     Γöé 3  Γöé 20 Γöé Jones Γöé 46324 Γöé
     Γöé 4  Γöé 30 Γöé Brown Γöé 98232 Γöé
     Γöé 5  Γöé 40 Γöé Call  Γöé 72355 Γöé
     Γöé 6  Γöé 40 Γöé King  Γöé 26355 Γöé
     Γöé 7  Γöé 50 Γöé Ward  Γöé 97805 Γöé
     ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
      Result


ΓòÉΓòÉΓòÉ 10.5.5. Unite ΓòÉΓòÉΓòÉ

Use Unite to define a join between two non-SQL data sources that includes all
the rows of the two data sources, but shows duplicate rows only once in the
result.

The two data sources must have the same column names, as each pair of
identically-named columns is linked automatically. If the linked key columns
contain identical values for a row, only values from the row in the second data
source are included in the result. If the linked key columns contain different
values, the rows from both data sources are included. The values in non-key
columns are not compared.

To ensure that all nonduplicate rows are included in the result, all columns in
the two data sources must be key columns.

In the example below, the linking columns are A and B, and matching rows are in
red However, the matched rows have only partially matching values in the
columns. Magenta represents values in the first data source that are different
from the corresponding values in the second.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé B   Γöé C   Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 20 Γöé Jones Γöé 46324 Γöé
Γöé 20 Γöé Jones Γöé 74554 Γöé  Γöé 40 Γöé Call  Γöé 72355 Γöé
Γöé 30 Γöé Brown Γöé 98232 Γöé  Γöé 50 Γöé Ward  Γöé 97805 Γöé
Γöé 40 Γöé King  Γöé 26355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ   Second data source
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

       ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
       Γöé A  Γöé B   Γöé C   Γöé
       Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
       Γöé 10 Γöé Smith Γöé 50322 Γöé
       Γöé 20 Γöé Jones Γöé 46324 Γöé
       Γöé 30 Γöé Brown Γöé 98232 Γöé
       Γöé 40 Γöé Call  Γöé 72355 Γöé
       Γöé 40 Γöé King  Γöé 26355 Γöé
       Γöé 50 Γöé Ward  Γöé 97805 Γöé
       ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
       Result


ΓòÉΓòÉΓòÉ 10.5.6. Outer Join ΓòÉΓòÉΓòÉ

Use Outer Join to define a natural join between two data sources that includes
all rows from the first data source, and all columns from both data sources,
except the linking column in the second data source. If, in the common linking
column, there is no corresponding row value in the second data source, null
values are shown for the remaining columns from the second data source.

This type of join is useful if you want to detect any problems in your
database, such as loss of data, or unknown values.

In the example below, the linking column is A and matching columns are in red.
The black areas in the joined table represent null values.

ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé A  Γöé B   Γöé C   Γöé  Γöé A  Γöé D  Γöé E    Γöé F    Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé 10 Γöé Smith Γöé 50322 Γöé  Γöé 30 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
Γöé 20 Γöé Jones Γöé 46324 Γöé  Γöé 50 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
Γöé 30 Γöé Brown Γöé 96232 Γöé  Γöé 70 Γöé AAA Γöé 621.70 Γöé 634.58 Γöé
Γöé 40 Γöé Call  Γöé 72355 Γöé  ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
Γöé 50 Γöé Davis Γöé 32553 Γöé   Second data source
Γöé 60 Γöé Reed  Γöé 82375 Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
 First data source

    ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ

   ΓöîΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
   Γöé A  Γöé B   Γöé C   Γöé D  Γöé E    Γöé F    Γöé
   Γö£ΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
   Γöé 10 Γöé Smith Γöé 50322 Γöé   Γöé     Γöé     Γöé
   Γöé 20 Γöé Jones Γöé 46324 Γöé   Γöé     Γöé     Γöé
   Γöé 30 Γöé Brown Γöé 96232 Γöé XYZ Γöé 500.32 Γöé 324.89 Γöé
   Γöé 40 Γöé Call  Γöé 72355 Γöé   Γöé     Γöé     Γöé
   Γöé 50 Γöé Davis Γöé 32553 Γöé AAA Γöé 234.53 Γöé 743.92 Γöé
   Γöé 60 Γöé Reed  Γöé 82375 Γöé   Γöé     Γöé     Γöé
   ΓööΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
    Result


ΓòÉΓòÉΓòÉ 10.6. Selecting, calculating and summarizing data ΓòÉΓòÉΓòÉ

After you link the data sources in a query, you can specify any of the
following to complete the query definition:

o Calculated columns
o Row selections
o Column selections
o Data grouping
o Data summaries

QUERY FOR OS/2 helps you to define your query in the most efficient way by
providing choices on the Query menu (and, when you select Advanced options, on
the Selected menu) in the order in which you should use them:

 1. Calculate columns
 2. Select rows
 3. Rename columns
 4. Select columns
 5. Define summary

You need not apply any of these actions to a query, but if you do apply one of
them, you cannot then select an action that appears higher in the list.

For example, suppose that a query is made up of two tables, TABLE1 and TABLE2,
linked with a join. If you apply a Select rows action to the query (by
selecting OK on the Select Rows window), it is not then possible to calculate
columns for the query. However, you can still rename or select columns for the
query.

Define summary is the last choice on the menu. After you define and apply
summary operations using the Define Summary window, you cannot select any of
the menu choices above it. However, you can temporarily suspend the summary
definition if you want to select these actions. To do this, select Suspend
summary on the Define Summary window.


ΓòÉΓòÉΓòÉ 10.6.1. Selecting rows and columns ΓòÉΓòÉΓòÉ

In most cases you can make your row and column selections by selecting and from
the tool bar. This is the same as selecting Select rows and Select columns from
the Query menu, and applies the selections to the entire query. In a few cases,
especially when linking data sources with a semi join, an except join, or an
outer join, you might want to select rows and columns from a partial query. See
Selecting data from a partial query for information on how to do this.


ΓòÉΓòÉΓòÉ 10.6.2. Calculating columns ΓòÉΓòÉΓòÉ

A calculated column contains data that is calculated row by row from data in
other columns, according to a calculation expression that you specify. The
calculated column does not exist in the original data sources, but is produced
when you run the query.

For example, suppose you had a column in a table that contained the cost price
of your products. You could create a calculated column that contains the retail
price by typing an expression to calculate a fixed mark-up:

"Retail Price" = "Cost Price"*1.5

To create a calculated column:

 1. Select from the tool bar. The Calculate Columns window is displayed.

 2. Type a name for the calculated column in Result name.

 3. Type a calculation expression to produce the data you want in Expression.
    See Point and click expression entry for information on how to use the
    Expression Builder to help you create an expression.

 4. Select Format to specify how numbers are shown in the calculated column.
    This step is optional.

 5. Select OK.


ΓòÉΓòÉΓòÉ 10.6.3. Grouping and summarizing data ΓòÉΓòÉΓòÉ

Grouping data means ordering it so that rows having the same value in a
specified column are shown together in a contiguous block or group. Grouping
data makes it easier to read, because related items are shown together. For
example, if you grouped a query using a column called STATE, you would see all
customers in, for example, Vermont, shown together in a block in the query
result.

To summarize data, you create a new column to contain a summary value. The
summary value is calculated using an expression that you specify. The following
summary types are predefined so that you can select them from a list:

o Minimum
o Maximum
o Sum
o Average
o Count

For more information on these summary types, see the online help for the Define
Summary window.

Summary values are calculated for the columns you specify and, if you have
created groups, for each group.

To group and summarize data:

 1. Select from the tool bar. The Define Summary window opens.

 2. Select the columns you want to use to create groups from the lists in
    Grouping columns.

    You can specify up to five columns, although only two are shown initially.
    The second and subsequent groups you specify are nested within the
    preceding level of grouping.

 3. Type a name for a new column to contain the summary data in New column.

 4. Select one of the predefined summary types from the list in Summary
    expression. This step is optional.

 5. Type the remainder of the expression (for example, a column name) to create
    the summary data in the entry field in Summary expression.

    See Point and click expression entry for information on how to use the
    Expression Builder to help you create an expression.

 6. Select OK.

A new column containing the summary data is added to the query result.


ΓòÉΓòÉΓòÉ 10.7. Selecting data from a partial query ΓòÉΓòÉΓòÉ

When you define a query using a semi join, an except join or an outer join, it
can be useful to make data selections from part of a query, rather than from
the entire query. For example, you could make data selections from the first
part of a query before linking it with an except join to another data source.
This gives you considerably greater accuracy in specifying the result of the
except join, and can save you time by avoiding the use of an embedded query.

By selecting Advanced options from the View menu, you can:

o Select data from a partial query. Most of the data selection choices from the
  Query menu are added to the Selected menu.

o Link a data source to itself.

To select data from a partial query:

 1. Select the data source at the end of the partial query.

 2. Select the data selections you want from the Selected menu. For example,
    you can select Select rows from the Selected menu.

    The selections you make are applied to the partial query as if it were a
    complete query.


ΓòÉΓòÉΓòÉ 10.8. Making a query reusable ΓòÉΓòÉΓòÉ

You can make a query more flexible by using variable values in its definition.
You tell QUERY FOR OS/2 which values to use for the variables when the query is
run.

For example, suppose you created a simple query to provide the address of a
given customer. You would use this query each time you wanted to find a
customer's address, but it would be tedious to have to open the query and
change the row selection expression every time.

By using a variable value in the row selection expression, however, you can
specify the customer name you want each time you run the query. Suppose you
typed the following row selection expression: "COMPANY" = `@Customer_name`

Now each time you run this query, the Value Substitution window is displayed.

Type the customer name of your choice, and select OK. The variable value in the
query definition is replaced with the name you type before the data is
retrieved.


ΓòÉΓòÉΓòÉ 10.8.1. Naming variable values ΓòÉΓòÉΓòÉ

A variable value must consist of an @ character followed by up to 20
characters. The characters must comprise alphanumeric characters or the
underscore character. The first character after the @ cannot be a number.
Spaces cannot be used within the variable. For example:

Valid                         Not valid
@var1                         @%1
@Customer_number              @Customer number
@Address1                     @1Address

You can use any number of variable values in expressions in the Select Rows and
Calculate Columns windows of a query.


ΓòÉΓòÉΓòÉ 10.8.2. Making variable values easier to use ΓòÉΓòÉΓòÉ

Use the following tips to make your variable values user-friendly:

o Use meaningful names for the variable values.

  Variable values are shown next to an entry field in the Value Substitution
  window when the query is run. It is much easier to remember what to type next
  to a value such as "Customer_name" than a value such as "var1".

o Provide a cue in the icon name.

  Include a word such as "any" in the query icon name to provide a visual
  reminder that your query asks the user to type values for its variables. For
  example, Weekly sales comparison - any 2 products.


ΓòÉΓòÉΓòÉ 10.9. Producing SQL using a query ΓòÉΓòÉΓòÉ

If you need to use an SQL SELECT statement, but are not familiar with SQL, you
can use a query to produce the SELECT statement for you. For example, you can
define an SQL view without writing any SQL by using a query to produce the
required SELECT statement.

To produce a SELECT statement:

 1. Define a query that produces the result you want.

 2. Select from the tool bar.

    An SQL Statement containing the SQL produced by your query is displayed.
    See Creating effective SQL statements for information about how to edit and
    save the SQL statement.

 3. Use the clipboard to cut and paste the SELECT statement into the window
    where you want to use it.


ΓòÉΓòÉΓòÉ 10.10. Importing Query Manager queries ΓòÉΓòÉΓòÉ

Query Manager is a query tool supplied with the IBM OS/2 Extended Services* and
IBM DATABASE 2 OS/2 products. You can convert a prompted query produced by
Query Manager into a Visualizer query by importing it:

 1. Select Import from the Query menu. The Import window is displayed.

    A list of all the SQL databases cataloged on your workstation is shown in
    the Locations list.

 2. Select a database from the Locations list.

    A list of all collection names associated with the selected database is
    shown in the Locations list. The collection name (sometimes called the
    table prefix) is usually the same as the user ID of the person who created
    the tables and views.

 3. Select a collection name from the Locations list.

    A list of queries identified by the selected collection name is shown in
    the Names list.

 4. Double-click on the query you want in the Names list.


ΓòÉΓòÉΓòÉ 10.11. Tips on defining queries for use by others ΓòÉΓòÉΓòÉ

When you define queries for others to run, there are a number of tips you can
follow to simplify both using and maintaining the queries. Some of these tips
are more obvious than others. The following sections give a brief summary of
them.


ΓòÉΓòÉΓòÉ 10.11.1. Naming icons ΓòÉΓòÉΓòÉ

Help your users identify a query by giving the desktop icon a meaningful name.
For example, if the query selects sales figures from tables updated weekly for
the ten best-selling products, a good title would be Top 10 sellers - Last
week.

For information on how to change the name of an icon, see Common OS/2 tasks.


ΓòÉΓòÉΓòÉ 10.11.2. Running an object by double-clicking ΓòÉΓòÉΓòÉ

If your users often run a query, but rarely or never make changes to it, you
can make life simpler for them by changing the default Open action. Your users
can then run the query by double-clicking on its icon.

To change the default Open action:

 1. Display the pop-up menu for the icon.

 2. Select the arrow to the right of Open.

 3. Select Settings. The query notebook is displayed.

 4. Select the Menu notebook tab.

 5. In the Available menus list, select ~Open.

 6. Select the Settings push button. The Menu Settings window is displayed.

 7. In the Default action list, select Run query.

 8. Select OK and close the query notebook.

Your users can still open the object in the usual way by using the pop-up menu
for the icon.


ΓòÉΓòÉΓòÉ 10.11.3. Preventing changes to a query ΓòÉΓòÉΓòÉ

If none of your users need to change the query, or if several users access the
same icon on a LAN, you can prevent any changes by making the query a read-only
file.

To make a query a read-only file:

 1. Open the settings notebook of the query.

 2. Select the File notebook tab.

 3. Select the right arrow at the bottom of the page to display Page 2 of the
    File section.

 4. In Flags, select Read-only, then close the notebook.

Alternatively, if the query is stored on a LAN resource, you can make the
entire resource read-only.


ΓòÉΓòÉΓòÉ 11. Accessing data using SQL Statements ΓòÉΓòÉΓòÉ

Use the SQL Statement object to create, run and store an SQL Statement, and to
view, manipulate, or print any result. You can use an SQL Statement defined by
someone else to provide data for a report without having to know SQL yourself.

You can drag and drop an SQL Statement onto the following objects:

o Visualizer report
o Folder
o Out-basket (electronic mail)
o Printer
o Shredder

This chapter is intended for those not familiar with SQL, and describes how to
use an existing SQL Statement as a data source for a report, and how to run an
SQL Statement and view the result.

If you are familiar with SQL, you can define an SQL Statement to select data
from tables and views, or to perform many routine database maintenance tasks.
You can store your SQL Statement and allow other users, who may have no
knowledge of SQL, to run it and use any result produced. For information on
these tasks, see Creating effective SQL statements.


ΓòÉΓòÉΓòÉ 11.1. Using an SQL Statement to provide data for a report ΓòÉΓòÉΓòÉ

You can use an SQL Statement to provide data for a report in the same way as a
table, view or query.

Drag the SQL Statement and drop it on an open report. You can drag either the
icon of a closed SQL Statement, or the title-bar small icon of an open SQL
Statement. The report is connected to the database specified in the SQL
Statement and is able to access the SQL Statement result.


ΓòÉΓòÉΓòÉ 11.2. Running an SQL Statement ΓòÉΓòÉΓòÉ

To run an SQL Statement without opening it:

 1. Display the pop-up menu for the SQL Statement.
 2. Select the arrow to the right of Open.
 3. Select Run SQL.
If Run SQL has a check mark next to it, it is the default selection on the
cascaded menu. In this case, you can run the SQL Statement by double-clicking
on its icon. For information on how to change the default selection on the
cascaded menu, see Tips on defining SQL statements for use by others.

To run an SQL Statement that is open for browsing or editing, select from the
tool bar.

If the SQL Statement contains a SELECT statement that runs successfully, the
result is shown in a data browsing window.

You can scroll through the data in this window, but you cannot update it. You
can save the result as a Visualizer table or in a number of other data formats
on the OS/2 file system. For more information, see Viewing and updating data in
an SQL table.


ΓòÉΓòÉΓòÉ 12. Analyzing and presenting data using reports ΓòÉΓòÉΓòÉ

Use a Visualizer Report object to analyze and present data from queries, SQL
tables and views, Visualizer tables, and SQL Statements in a textual form.
Choose the elements you want to show in your report, and change the text,
color, size, spacing, and other settings of each report element by
double-clicking on it. You can add headings, annotations, color and fonts to a
report to make an eye-catching presentation to your colleagues. When you
finish, print your report by dragging and dropping it on the printer.

You can drag and drop a Visualizer report onto the following objects:

o Folder
o Out-basket (electronic mail)
o Printer
o Shredder

This chapter describes how to select and group data in a report, how to analyze
the data in different ways, how to make a report more attractive, and how to
print and save a report.


ΓòÉΓòÉΓòÉ 12.1. Selecting data for your report ΓòÉΓòÉΓòÉ

You can use one of the following data sources to create a report:

o An SQL table
o An SQL view
o A Visualizer query
o An SQL statement
o A Visualizer table
o A file stored in the OS/2 file system in a supported data format
o An Application System (AS) table, or a table that can be accessed using AS

To use more than one data source in a report, create a query that combines the
data sources, then use the query to create your report.


ΓòÉΓòÉΓòÉ 12.1.1. Selecting data source icons ΓòÉΓòÉΓòÉ

Usually, you can see the icon of the data source you want. For example, it
might be in a database object or on the desktop.

To select a data source for your report when you can see its icon, drag the
icon and drop it on an open report.


ΓòÉΓòÉΓòÉ 12.1.2. Finding data sources for your report ΓòÉΓòÉΓòÉ

Sometimes you may not be able to see the icon of the data source you want. It
might not exist on your workstation, or you might not know where it is.

If you cannot find the icon you want, select from the tool bar. The Select a
Table or Query window is displayed.

Use the Select a Table or Query window to find data sources on the OS/2 file
system, and to find other data sources that have been cataloged on your
workstation.

To select a table or a view from an SQL database:

 1. Ensure that SQL is selected in the Type list. (SQL is the default
    selection).

    A list of all the SQL databases that you have cataloged on your workstation
    is shown in the Locations list.

 2. Select a database in the Locations list.

    A list of all collection names associated with the selected database is
    shown in the Locations list. The collection name (sometimes called the
    table prefix) is usually the same as the user ID of the person who created
    the tables and views.

 3. Select a collection name in the Locations list.

    A list of tables and views identified by the selected collection name is
    shown in the Names list.

 4. Double-click on the table or view you want in the Names list.

For information on using the Select a Table or Query window to find data
sources that are not stored in an SQL database, see the online help for the
window.


ΓòÉΓòÉΓòÉ 12.1.3. Selecting columns for a report ΓòÉΓòÉΓòÉ

You do not have to show all of the columns in a data source in your report. You
can select specific columns to show. The other columns are still available to
use when creating text variables and calculated columns, or when selecting or
ordering rows.

To select columns:

 1. Select from the tool bar.

 2. In the Report columns list, select the check boxes next to the names of the
    columns you want shown in your report. The columns are displayed across the
    report in the order in which you select them.

 3. Select OK.


ΓòÉΓòÉΓòÉ 12.1.4. Selecting rows for a report ΓòÉΓòÉΓòÉ

Just as you can choose to show only some of the columns in the data source, you
can also choose to select only some of the rows of data. You might want to
select rows because:

o You are only interested in certain items in the data, and don't want to look
  at all the items. For example, you have data about lots of products, but are
  only interested in those sold under a specific brand name.

o You want to highlight items that need your attention. For example, it would
  be useful to know which of your account customers have not paid their bills
  for more than 3 months.

o You want to look at data that has so many rows that it would need more memory
  than you have in your workstation. For example, you have a huge mainframe
  database of daily sales figures for stores in major cities across the
  country. You can look at the data for each city in turn, or for each separate
  product line countrywide.

o You want to use only a sample of data to save time while designing your
  report. Although you could do this by explicitly selecting rows, a simpler
  way is described in Redrawing a report faster.

To select rows:

 1. Select from the tool bar. The Select Rows window is displayed.

 2. Type an expression that specifies the rows you want.

    For example, the expression shown above specifies that only rows where the
    depot is New York or Montreal should be selected.

    Type your expression using the syntax indicated in brackets above the
    Expression field. If you aren't familiar with the syntax indicated, see
    Point and click expression entry.

 3. In the Select Rows window, select OK.


ΓòÉΓòÉΓòÉ 12.2. Grouping data in your report ΓòÉΓòÉΓòÉ

You can make your report more useful by arranging the data into meaningful
groups.

Grouping data means ordering it so that rows having the same value in a
specified column are shown together in a contiguous block or group. Grouping
data makes the data easier to read, because related items are shown together in
the same place.

The column you specify to order data in this way is referred to as the column
controlling the group. When the data has been ordered, whenever the value in
this column changes, a new group is created. Because of this, groups are often
referred to by the common value they share. For example, the "Department 40"
group.

Totals and other data analyses, when specified, are calculated at the end of
the appropriate groups. However, they always use data from columns that do not
control groups.

For example, if you are creating a report from personnel data, it might be
useful to group staff by division, then by department within that division, and
then by job title within that department. Providing you have columns set up for
Division, Department, and Job Title, you can create these groups in your report
regardless of the order of the original data.

In this example, you might want to see the total salary for each department in
your report. The total salary is calculated whenever the value in the
Department column changes, and a new group begins. However, the total uses data
from the Salary column, which does not control grouping in the report.

You can group data down or across your report.


ΓòÉΓòÉΓòÉ 12.2.1. Grouping data down ΓòÉΓòÉΓòÉ

Grouping data down a report means that when a new group is started, it is shown
below the preceding group, after any totals for that group. Most reports group
data in this way. You can specify up to five columns to control grouping down a
report.

To group data down a report:

 1. Select from the tool bar. The Select Columns and Define Groups window is
    displayed.

 2. In Columns controlling groups down, select a column to control grouping in
    the Group 1 list.

 3. If you want your data to be ordered into more than one level of grouping,
    select the columns you want to control the grouping from the appropriate
    Group lists. The second and subsequent levels of grouping are nested within
    the preceding level.

 4. Select OK.

As well as creating up to five levels of grouping down your report, you can
also create one level of grouping across the report.


ΓòÉΓòÉΓòÉ 12.2.2. Grouping data across ΓòÉΓòÉΓòÉ

Like data that is grouped down a report, data that is grouped across causes a
new group to be started, and totals to be calculated, whenever there is a
change in the value of the column that controls the grouping. When data is
grouped across, a new group is shown to the right of  the preceding group,
after any totals for that group.

For example, if you group across on a column called Sex, your report will show
separate totals across the page for Male and Female, and a further total for
both sexes. This arrangement means that you can combine a group across with one
or more levels of grouping down to produce a cross-tabular report.

To create a group across a report:

 1. Select from the tool bar. The Select Columns and Define Groups window is
    displayed.

 2. Select a column from the list in Column controlling a group across. Only
    one column can be used to control grouping across a report.

    Grouping across causes the report to become a summary report. A summary
    report shows only totals for groups, rather than information from
    individual rows in the data. Because of this, you must select some totals,
    or there will be nothing to show in your report.

 3. Select the Set column totals push button. The Column Totals Settings window
    is displayed.

 4. Select the total types you want for each column. For more information, see
    Naming variable values.

 5. Select OK.

 6. In the Select Columns and Define Groups window, select OK.


ΓòÉΓòÉΓòÉ 12.3. Analyzing your data ΓòÉΓòÉΓòÉ

Report provides a number of calculation functions to help you analyze your
data. You can:

o Show summary information using total types such as average, high, low, count,
  and standard deviation.

o Calculate information from data in each row of the report. A calculated
  column uses an expression you provide to calculate a new column from data in
  existing columns.

o Annotate or highlight selected rows. You can use conditional notes to
  annotate selected rows of your report. You can also show selected rows in a
  different color or font.


ΓòÉΓòÉΓòÉ 12.3.1. Summarizing data using column totals ΓòÉΓòÉΓòÉ

To produce totals for a column in your report:

 1. Select from the tool bar. The Report Settings window is displayed.

 2. Select the Settings push button for Totals.

 3. On the Column Totals Settings window, select Totals to use the following
    total types:

   o Sum
   o Count
   o High
   o Low
   o Average
   o Standard deviation
   o Calculate

    Select Percentages to calculate totals as percentages. You can use
    percentage totals in addition to any other totals. For an explanation of
    the percentage total choices, see the online help for the window.

 4. Select the name of the column you want to total in Column name.

 5. Select either the Absolute or Cumulative check box for the total types you
    want.

    Absolute totals are calculated only from data in the current group in the
    report. Cumulative totals are calculated from data in the current and all
    preceding groups in the report.

 6. Type a label to identify each total in your report. You can use any text
    variable in your total labels. This step is optional.

 7. Repeat for any other column you want to total by selecting it in Column
    name.

 8. Select OK.

 9. Select OK to close the Report Settings window.

The most efficient way of producing totals is to select them after you have
selected columns and rows and defined any grouping in your report. Instead of
closing the Select Columns and Define Groups window, select the Set column
totals push button.


ΓòÉΓòÉΓòÉ 12.3.2. Calculating data for new columns ΓòÉΓòÉΓòÉ

Use calculated columns to provide additional information from data contained in
existing columns. For example, you can create a column which calculates an
employee's total salary from two existing columns which show their basic salary
and commission. This is a simple example, but you can use more complex
expressions to formulate a calculated column.

To calculate additional columns from existing ones:

 1. Select from the tool bar. The Calculate Columns window is displayed.

 2. Type a name for the new column in Result name.

 3. Type an Expression that defines your calculated column, or use Expression
    Builder by selecting . For further information on using Expression Builder,
    see Point and click expression entry.

 4. Select Format to control the numeric display format of your new column.

 5. Select OK to close the Calculate Columns window.


ΓòÉΓòÉΓòÉ 12.3.3. Annotating your report ΓòÉΓòÉΓòÉ

Use conditional notes to annotate values fulfilling a specified condition. For
example, in a report of credit card balances, you could add the words Above
credit limit when the value in the Balance column was higher than that in the
Credit_Limit column.

To set conditional notes:

 1. Double-click on the column you want to annotate.

 2. On the Column Settings window, type a Condition in the Conditional
    presentation section of the window, or use Expression Builder by selecting
    .

 3. Type the text of your annotation in Note.

 4. If you want to change the color of the data, as well as or instead of
    showing your annotation text, select the Color push button.

 5. Select either Replace value with note or Show both value and note.

 6. Check that the width of the column, set in the Column elements display area
    section of the window, is sufficient to display your text.

 7. Select OK.


ΓòÉΓòÉΓòÉ 12.4. Finding information in your report ΓòÉΓòÉΓòÉ

You can easily find specific pages and group totals in your report.

To find a specific page or part of your report:

 1. Select Find page from the Edit menu.
 2. Type the page and part number you want to find.
 3. Select Find.

You can find the first or last page of your report more quickly by selecting
First page or Last page from the Edit menu.

To find a specific group total in your report:

 1. Select Find group total from the Edit menu.

 2. Type a value for the total you want to locate against the appropriate
    column name.

 3. Select Find.


ΓòÉΓòÉΓòÉ 12.5. Adding text to your report ΓòÉΓòÉΓòÉ

You can make your report easier to read and understand by adding text headings
and labels to the data. You can type text into any of the following areas of a
report:

Top title
          Appears at the top of each page and part of your report.

Column heading
          Appears at the top of each page and part of your report, and
          optionally, at the top and bottom of each group.

Group title
          Appears once for each group, before any column headings or details.

Group total heading
          Appears above the totals for each group.

Group label
          Appears on the left of the report, on the same line as the totals for
          each group.

Group total footer
          Appears below the totals for each group.

Bottom title
          Appears at the bottom of each page and part of your report.


ΓòÉΓòÉΓòÉ 12.5.1. Describing a report in top and bottom titles ΓòÉΓòÉΓòÉ

Top and bottom titles appear at the top and bottom of each page and part of
your report. Use them to provide a title for your report, and to provide status
information such as the date the report was produced, and its security
classification. Use them also to provide page and part numbers for easy
reference.

Because the supplied Report template already includes a top title, the
following steps describe how to add a bottom title to your report. However, the
steps are similar in both cases.

To add a bottom title to your report:

 1. Select from the tool bar.

    The Report Settings window is displayed.

 2. Select the Settings push button for Bottom title.

    The Bottom Title Settings window is displayed.

 3. Type your title in Text elements. You can use text variables in any line of
    the top title.

 4. Select the Alignment for each element of the bottom title in Text element.

 5. Ensure that the Display check box is selected in Bottom title.

 6. Select OK.

 7. Select OK to close the Report Settings window.

If there is a top or bottom title on your report already, you can open its
settings window by double-clicking on it.


ΓòÉΓòÉΓòÉ 12.5.2. Describing columns ΓòÉΓòÉΓòÉ

Column headings appear as descriptive text above each column in your report.
Use them to describe the column contents in your own words, rather than having
to use the column names found in the original data source.

To add column headings to your report:

 1. Select from the tool bar.

 2. Select the Settings push button for Column headings.

 3. On the Column Headings Settings window, type the text you want to appear in
    your first column heading in Heading text.

 4. Select the next column for which you want to add a heading from the Column
    name list.

 5. Type the column heading text in Heading text.

 6. Repeat steps 4 and 5 for each column for which you want a heading.

 7. Choose how often you want the column headings to appear in your report by
    selecting an option in Headings for all columns.

 8. Select OK.

 9. Select OK to close the Report Settings window.

If there are column headings already in your report, you can change their
settings by double-clicking on them.


ΓòÉΓòÉΓòÉ 12.5.3. Describing a group ΓòÉΓòÉΓòÉ

Group titles appear at the top of each group, before any column headings or
details for the group. Use them to describe the group.

To add group titles to your report:

 1. Select from the tool bar.

 2. Select the Settings push button for Groups down.

 3. Select the Settings push button for Group title.

 4. On the Group Title Settings window, select the group you want to title from
    the Group list.

 5. Type your group title in Text elements. You can use text variables in any
    line of the group title.

 6. Select the Alignment for each element of your group title in Text element.

 7. Ensure that the Display check box is selected in Group title.

 8. Select OK.

 9. Select OK to close the Report Settings window.

If there is a group title already in your report, you can change its settings
by double-clicking on it.

You can use text variables that represent the grouping level of the column
effectively in the text of group titles. For details, see Summarizing data
using column totals.


ΓòÉΓòÉΓòÉ 12.5.4. Describing totals ΓòÉΓòÉΓòÉ

A group total area appears below each group when you have selected totals for
one or more columns. Text in total labels or column headings can appear in a
group total area, but the text must be identical for all group total areas in
your report. You can type text that is specific to each group total area in any
of the following areas:

o Group total heading
o Group total label
o Group total footer

Few reports need to use all three of these at the same time. Use those that
allow you to position your text where you need it.


ΓòÉΓòÉΓòÉ 12.6. Changing fonts ΓòÉΓòÉΓòÉ

You can change the font of any text or data in your report to one of the
following:

o Courier
o Times Roman
o Helvetica

You can also choose to add any combination of bold, italic or outline emphasis
to any report element.

To change the font or emphasis of a report element:

 1. Select the element you want to change.

 2. Select Font from the Selected menu. The Font window is displayed.

 3. Select the font you want from the list, and select any combination of
    emphasis check boxes. An example of how your text will look is shown.

 4. Select OK.

 5. Click on the report element to deselect it.
A Font push button appears on most of the windows in which you can enter text.
Select it to choose a font as you enter your text. To change the size of the
fonts used in your report, use the tool bar buttons. Select to decrease the
size of the fonts, and to increase the size of the fonts. Each time you click
on one of the buttons, the font size is changed by approximately 20%.


ΓòÉΓòÉΓòÉ 12.7. Adding color to your report ΓòÉΓòÉΓòÉ

Use color in combination with font and emphasis to highlight information in
your report.

To change the color of any report element:

 1. Select the report element.

 2. Select Color from the Selected menu. The Color window is displayed.

 3. Change the background, foreground or outline of the selected report element
    by selecting a radio button and then selecting the color you want.

 4. Select OK.

 5. Click on the report element to deselect it.


ΓòÉΓòÉΓòÉ 12.8. Changing text in your report ΓòÉΓòÉΓòÉ

When you open a report, it retrieves the latest data from the data source you
have selected. Because the report always shows the latest information, it is
sometimes useful to be able to update the text in titles and labels in the
report to suit the data.

The following sections describe two ways of changing report text by using
variables. A variable is a place-holder for text that changes. The actual text
is updated each time the report is started.


ΓòÉΓòÉΓòÉ 12.8.1. Including data in text ΓòÉΓòÉΓòÉ

Text variables take the form of an underscore character followed by a number.
You can type them in any text area of the report (except in column headings)
just like normal text. When the report is opened, the text variable is replaced
by the actual text that it represents. There are predefined text variables for
text such as the current date, time, and page number. The current value in any
grouping column is also available as a predefined text variable. For example,
in a report grouped by Region and then by Division, _1 would give the current
value (such as North East) in the Region column, and _2 would give the current
value in the Division column.

You can use text variables that represent the grouping level of the column
effectively in the text of group titles. Suppose you had a personnel report of
salaries and commissions, grouped down by Region, then by Division, and finally
by Department. The following text in a group title would give anyone reading
the report for the first time a clear idea of what the figures represent:

Salaries and commission for Computing Department, Division 40
                                            North-West Region

To achieve this, type the following in Text elements in the Group Title
Settings window:

Salaries and commission for _3 Department, Division _2
                                             _1 Region

For more information on text variables, see the online help for the Report
object.


ΓòÉΓòÉΓòÉ 12.8.2. Changing text when starting a report ΓòÉΓòÉΓòÉ

If you use a report with data that is regularly updated, you might also want to
change the text used in the report to reflect the latest data. For example, if
you used the same report each month to show your five best-selling products,
you would want to change the name of the month in the top title of the report.
You could do this by opening the report, and changing the text in the Top Title
Settings window. However, if there are several report elements you want to
change regularly, there is an easier way.

When you type text elements, you can use variable values in the text. These
take the form of an @ character, followed by an alphabetic character and up to
19 other characters, not including a space. For example, @Month

When you open a report in which you have used variable values, the Value
Substitution window opens. Use this window to specify the actual text you want
to use in your report. Using variable values enables you to save time by typing
all the text you want to update in one window. For more information on variable
values, see Making a query reusable and Making variable values easier to use.


ΓòÉΓòÉΓòÉ 12.9. Including your report in a document ΓòÉΓòÉΓòÉ

If you included your report in a word-processed document, it would be tedious
to update the document each time the report changed. However, if your word
processing software supports dynamic data exchange (DDE), you can update your
document automatically, without leaving the Report window.

To include your report in a document:

 1. If anything is selected in the report, click on it to deselect it.

 2. In the Report window, select Copy from the Edit menu.

 3. Click on an insertion point for your report in the word processor document.

 4. Select Paste link or Paste special from the word processor's Edit menu.

    The exact method for creating a DDE link varies between different software
    products, although this step will work for most. If you cannot see these
    menu choices, see the documentation for your word processing software.

To update the report in the document, select Refresh clients from the report's
Edit menu.


ΓòÉΓòÉΓòÉ 12.10. Tips to speed up report creation ΓòÉΓòÉΓòÉ

There are several ways to define a report faster. You will probably find your
own shortcuts as you work, but this section gives some suggestions to get you
started.


ΓòÉΓòÉΓòÉ 12.10.1. Retrieving data faster ΓòÉΓòÉΓòÉ

It is much quicker to make any row selections for your report before selecting
columns. To do this, use the Select rows push button in the Select Columns and
Define Groups window before selecting OK or Set column totals.

In some cases, particularly with remote databases, it is also quicker to create
any calculated columns by selecting from the tool bar before using the Select
Columns and Define Groups window.


ΓòÉΓòÉΓòÉ 12.10.2. Changing a report layout quickly ΓòÉΓòÉΓòÉ

When designing your report layout, use the following tips to speed your work.


ΓòÉΓòÉΓòÉ 12.10.2.1. Changing report settings quickly ΓòÉΓòÉΓòÉ

You can easily change the position, text, and other settings of a report
element simply by double-clicking on it. For example, if you want to change the
your report's top title, just double-click on it. The Top Title Settings window
is displayed, and you can type your new title in the Text element list. You can
double-click on any report element, including totals and separators.

If you need to change the settings for several column-related report elements,
such as column headings or group total areas, you can save time by making all
the changes in the same window.

To change the text for several column headings:

 1. Double-click on the nearest column heading, even if it is one you do not
    want to change.

 2. In the Column Heading Settings window, select the first column for which
    you want to change the heading settings in the Column name list.

 3. Make your changes to the settings.

 4. Select the next column in the Column name list.

 5. Change the settings.

 6. Repeat steps 4 and 5 for all the column headings you want to change.

 7. When you have changed the settings for the final column heading, select OK.


ΓòÉΓòÉΓòÉ 12.10.2.2. Redrawing a report faster ΓòÉΓòÉΓòÉ

If you want to see the effect of each change you make to your report layout as
you are designing it, use a small sample of your table or query. The report is
then redrawn faster.

To use sample data for your report:

 1. Select Open settings from the Report menu.

 2. In the Report Settings window, select Show sample only, and type in the
    number of rows of your table or query that you want to work with.

 3. Select OK.

Remember to deselect Show sample only before producing your final report.


ΓòÉΓòÉΓòÉ 12.10.2.3. Preventing a report being redrawn ΓòÉΓòÉΓòÉ

If you are making a number of changes to the layout of your report and do not
need to see the effect of each individual change, deselect Autorefresh report
from the View menu. This prevents the report from being redrawn each time you
make a change.

To see what your changes look like, either select Refresh report from the View
menu to refresh manually, or select Autorefresh report again.


ΓòÉΓòÉΓòÉ 12.11. Importing Query Management Facility forms ΓòÉΓòÉΓòÉ

If you use Query Management Facility (QMF), you can save time by using the
formatting options in existing QMF forms as the basis for your Visualizer
report layouts. The QMF form is converted as accurately as possible into an
equivalent Visualizer report.

You can import a QMF form created with QMF version 2.4 or later into a
Visualizer report, provided that the form is stored in the OS/2 file system. To
import a form from an earlier version of QMF, export the form again from one of
the supported versions of QMF.

To import a QMF form:

 1. Select a data source for your report. The Select Columns and Define Groups
    window is displayed.

 2. Select Cancel

 3. Select Import from the Report menu.

    The Import Format window opens.

 4. Select QMF form.

 5. Select Import. The Import window is displayed.

 6. Select the database in which the form is stored.

 7. Select the collection name used for the form.

 8. Select the name of the form.

 9. Select OK.

For further information on importing QMF forms, see Using QMF forms with Query
for OS/2.


ΓòÉΓòÉΓòÉ 12.12. Printing your report ΓòÉΓòÉΓòÉ

To print a report, drag it and drop it on a printer object. You can drag either
the icon of a closed report, or the title-bar small icon of an open report. You
are not asked to provide any printing options unless you have selected the Job
dialog before print check box in the printer object's settings notebook.

Alternatively, you can:

 1. Select Print from the Report menu.

    If you have deselected Review printing options in the QUERY FOR OS/2
    Profile notebook, your report is now printed using the OS/2 default
    printer. If Review printing options is selected (this is the default
    setting), the Print Settings window is displayed.

 2. On the Print Settings window, choose whether to print the whole report, a
    sample of the report, or just the current page. By selecting Sample only,
    you can specify a limited number of rows from the start of your data table.
    This enables you to save time and paper by printing a small test report
    before using all of your data.

 3. Select a radio button in Pagination to choose whether you want the report
    page settings or the printer form settings to determine the pagination of
    your report.

 4. Select OK. The Printer Selection window is displayed.

 5. Select a printer or plotter from Printers.

 6. Select Print. The Print Copies window is displayed.

 7. Type the number of copies (1 through 99) you want to print.

 8. Use Options to change settings which are specific to your printer, such as
    the paper feed, size, orientation, or the print resolution. When you have
    selected the options you require, select Print on the Print Copies window.


ΓòÉΓòÉΓòÉ 12.13. Saving your report ΓòÉΓòÉΓòÉ

You can save your report in one of four ways:

o As a Visualizer report

  Your report layout is saved, together with the name and location of the data
  source used. The data itself is not saved, and when you next open this report
  it will show the most up-to-date information from the data source.

  To save your report with its current file name and location, select Save from
  the Report menu.

  To save your report with a different file name, or in a different location:

    1. Select Copy to from the Report menu.
    2. Type a new location and file name for your report.
    3. Select Visualizer report in Format.
    4. Select OK.

o As a plain (ASCII) text file.

  Your report, including all of the current information it is displaying from
  the table or query, is saved in a form that can be used with other objects,
  such as a word processor or spreadsheet. When you next use this report it
  will show the same data as when you saved it. This format saves only the text
  and spacing of the report. Colors, outlines, and fonts are not saved.

o As a print file

  The same as a plain text file, but with commands added to tell your printer
  when to start a new page.

o As a results table.

  Only the totals (such as sum and count) in your report are saved, in a
  Visualizer table. A results table is a compact way of saving summaries of
  reports based on frequently updated data.

  You can save your report as a Visualizer report results table only if your
  report has totals selected from the Column Totals Settings window.

To save your report as a plain text file or results table:

 1. Select Copy to from the Report menu.
 2. Type a location and file name for the plain text file or results table.
 3. Select the Format your report will be saved in.
 4. Select OK.
 5. If you are saving your report as a plain text file:

    a) Select a pagination method for your report in Page size.
    b) Select OK.

You can save your report in any of the four formats as many times as you want.
If you do save more than once, remember to use a different file name each time.


ΓòÉΓòÉΓòÉ 13. Choosing the best report for your data ΓòÉΓòÉΓòÉ

Visualizer Report enables you to create many different report layouts so that
you can analyze and present your data in the most effective way. This chapter
provides some advice on which report type to choose and describes how to create
simple detail and summary reports, mailing labels, and spreadsheet reports from
existing data sources. It also describes how to customize any of these types of
report to suit your needs.


ΓòÉΓòÉΓòÉ 13.1. Choosing an effective report for your data ΓòÉΓòÉΓòÉ

You can use Visualizer Report to produce the following types of report:

Detail reports
            A detail report shows data from individual rows of the data source.

            Use a detail report when you need to break down an analysis to its
            lowest level, or when you need to look up information about
            individual items in your data.

Summary reports
            A summary report shows only totals and other summaries of data from
            the data source. It does not show information relating to any
            individual row of data.

            Use a summary report when you want to analyze information at a
            higher level, looking at trends in the results for groups of items
            in your data.

Cross-tabular reports
            A cross-tabular report analyzes data in two dimensions, like a
            spreadsheet. Totals and other summaries appear down the side and
            along the bottom of the report. A cross-tabular report usually
            shows only summary information, but you can create one that shows
            details.

            Use a cross-tabular report when you need to summarize your data in
            more than one way at a time. For example, when you want to analyze
            expenses both by category and by month.

Mailing label style reports
            A mailing label style report shows data from each row as a block
            taking up several lines of a page, instead of showing it as a
            single line.

            Use a mailing label style report to produce mailing labels or
            record cards.

Customized reports
            A customized report is a nontabular, free-form report that you
            design yourself. You can create a customized report from any of the
            above types of report simply by moving columns and column headings
            to different positions.

            Use a customized report when you need information laid out in a
            specific, nontabular format.


ΓòÉΓòÉΓòÉ 13.2. Creating a detail report ΓòÉΓòÉΓòÉ

A detail report shows data from individual rows of your data source. For
example, a detail report of employee data would contain a line for each
individual employee. This example shows you how to create a detail report that
groups your data into relevant sections, with totals for each section. You can
create a detail report in just six basic steps:

 1. Create a new report, and rename it. See Common OS/2 tasks for information
    on how to do this.

 2. Open the report.

 3. Select a data source for your report by dragging its icon and dropping it
    on the report. The Select Columns and Define Groups window is displayed.

    If you cannot find an icon for the data source you want, see Finding data
    sources for your report.

 4. In the Report columns list, select the check boxes next to the names of the
    columns you want shown in your report.

    Columns are displayed in the report from left to right in the order in
    which you select them.

 5. Group the data down your report by selecting one or more columns from the
    lists in Columns controlling groups down. For an explanation of data
    grouping, see Grouping data in your report.

 6. Select any total types you want from the Column Totals Settings window. To
    open this window, select the Set column totals push button.

    For more information about using this window, see

An example of the kind of report that is produced is shown below. You can
fine-tune your report further by double-clicking on any report element and
making the changes you want. For example, to give the report a more meaningful
top title, such as Employee Salaries by Division, double-click on the top title
and retype the text.


ΓòÉΓòÉΓòÉ 13.3. Creating a simple summary report ΓòÉΓòÉΓòÉ

A summary report shows an analysis of data from individual rows in your data
source, but does not show individual rows from the data. For example, a summary
report of employee data might show the average salary and total salary bill for
each department, but it would not show the salaries of individual employees.

To create a simple summary report:

 1. Create a detail report as described in Ensure that you select the total
    types you want to show in your summary report.

 2. Select from the tool bar. The Report Settings window is displayed.

 3. In the Report Settings window, deselect the Display check box for Detail
    area.

 4. Select OK.

Your report is redrawn, showing only the totals you selected.


ΓòÉΓòÉΓòÉ 13.4. Converting a detail report to a summary report ΓòÉΓòÉΓòÉ

Using any detail reports you have, you can quickly create summary reports to
help you spot trends and stay focused on the big picture.

You can create a summary report from a detail report in two ways:

o By creating a simple summary report:

    1. Select from the tool bar.
    2. Deselect the Display check box for Detail area.
    3. Select OK. The report layout remains the same, but no details are shown.

o By creating a cross-tabular report:

    1. Select from the tool bar.

    2. Select a column in the list for Column controlling a group across. If
       the column you require is not in the list, you must first deselect it in
       Columns controlling groups down by selecting (none) in the Group list
       that contains your chosen column name.

       If you are unsure which column to choose, try using the one that
       contains the fewest unique values. For example, a column called Sex can
       contain only two possible values: male or female.

    3. Select OK.

No details are shown, but text and totals are preserved in the cross-tabular
report.


ΓòÉΓòÉΓòÉ 13.5. Creating a cross-tabular report ΓòÉΓòÉΓòÉ

This section explains how to create a cross-tabular summary report by grouping
data across the report. For information on how to create a cross-tabular detail
report, see Creating a financial report.

A cross-tabular report can have totals in two dimensions. For example, the
report below shows the number of parts in stock at each depot. If you scrolled
to the right of the report, you would see totals for the number of parts (of
all types) in each depot. If you scrolled to the bottom of the report, you
would see totals for the number of each part in all depots. In the bottom right
corner of the report, you would see the grand total of all parts in all depots.

This section explains how to create a cross-tabular summary report by grouping
data across the report. For information on how to create a cross-tabular detail
report, see Creating a financial report.

To create a cross-tabular summary report:

 1. Create a new report and rename it.

 2. Open the report.

 3. Select a data source for your report. The Select Columns and Define Groups
    window is displayed.

 4. In the Select Columns and Define Groups window, select the columns you want
    shown in your report. As a minimum, select the following three columns:

   o The column corresponding to the category you want to analyze across the
     report. In the picture below, this is the PARTNO column.

   o The column corresponding to the category you want to analyze down the
     report. In the picture below, this is the DEPOT column.

   o The column containing the numerical information you want to analyze. In
     the picture below, this is the STOCK column.

 5. Group the data down and across your report. Select the columns
    corresponding to the two categories by which you want to analyze your data.

 6. Select the Set column totals push button, then select the totals you
    require in the Column Totals Settings window. You must select at least one
    total type, or there will be nothing in your report to display. In the
    example shown, the Sum total type is selected for the STOCK column.

 7. In the Column Totals Settings window, select OK.

 8. Select OK to close the Select Columns and Define Groups window.


ΓòÉΓòÉΓòÉ 13.6. Creating mailing labels ΓòÉΓòÉΓòÉ

A mailing label style report can have any number of details across the page,
and does not follow the usual tabular column and row pattern. For example, you
can create address labels for printing on sheets of pre-cut self-adhesive
labels.

In the example that follows, the labels are printed two across and eight down
the page, to fit a label size of 3.9 inches by 1.3 inches (99 millimeters by 34
millimeters). This is a typical size for laser printer labels, but the report
can be adjusted to suit most label sizes.

To create a mailing label style report:

 1. Create a new report and rename it.

 2. Open the new report.

 3. Select a data source for your report. The Select Columns and Define Groups
    window is displayed.

 4. Select the columns you want shown in your report. The first column you
    select will become the first line of your address labels, the second column
    will become the second line, and so on.

 5. Specify how many labels you want across and down the page:

    a) Select from the tool bar.

    b) Select the Settings push button for Detail area.

    c) In the Detail Area Settings window, select User-defined in Number of
       detail areas in Down.

    d) Type 8 in the entry field.

    e) Type 3 in Number of blank lines below.

    f) Select User-defined in Number of detail areas in Across.

    g) Type 2 in the entry field.

    h) Type 17 in Number of character spaces after.

    i) Select OK.

    j) Select OK to close the Report Settings window.

 6. Select from the tool bar.

 7. Remove any unwanted text from your report:

    a) Double-click on one of the column headings, which now appear down the
       left side of the window.

    b) In the Column Headings Settings window, select None in Rotated display -
       Headings across.

    c) Select OK.

    d) Double-click on the top title of your report.

    e) In the Top Title Settings window, deselect the Display check box in Top
       Title.

    f) Select OK.

 8. Adjust the spacing to suit your labels.

    a) Select from the tool bar. The Page Settings window is displayed.

    b) Select the size of your sheet of labels (for example, Letter or A4) from
       the Paper list.

    c) Select Portrait in Orientation..

    d) In Margins, select Inches.

    e) Type 0.17 in Top.

    f) Type 0.75 in Left.

    g) Type 0 in Bottom.

    h) Type 0 in Right.

    i) Select OK.


ΓòÉΓòÉΓòÉ 13.6.1. Changing the label format ΓòÉΓòÉΓòÉ

You can easily change this example to suit other label sizes. Use the Detail
Area Settings window and the Page Settings window to specify the spacing
requirements for your labels.

In the Detail Area Settings window:

Use:                                    To change:

Number of detail areas down             The number of labels printed down one
                                        sheet

Number of blank lines below             The spacing between a label and the one
                                        below it

Number of detail areas across           The number of labels printed across one
                                        sheet

Number of character spaces after        The spacing between a label and the one
                                        beside it

In the Page Settings window:

Use:                     To change:

Top                      The horizontal alignment of the printed labels

Left                     The vertical alignment of the printed labels

You will probably need to experiment with these settings to print the labels
just how you want them. While you are experimenting, you can save time and
labels by specifying the number of labels that will fit on one sheet as the
sample size when you select Print.


ΓòÉΓòÉΓòÉ 13.6.2. Addressing envelopes directly ΓòÉΓòÉΓòÉ

If your printer has a feed for envelopes, you can print addresses directly onto
the envelopes:

o In the Detail Area Settings window, type 1 in both the User-defined entry
  fields.

o In the Page Settings window, select the envelope size you want to use from
  the Paper list.

o In Margins entry fields, type values appropriate to your envelopes for the
  top and left margins.


ΓòÉΓòÉΓòÉ 13.7. Creating a financial report ΓòÉΓòÉΓòÉ

When you create a report to show financial information, you may find you have
particular data and formatting requirements. For example, you might want to use
data from a spreadsheet to create your report, and change the way numbers are
displayed so that they are shown with a currency symbol and two decimal places.
You may find it easier to analyze your finances if as many columns as possible
are made to fit onto a standard sheet of paper.

This section explains how to create a typical spreadsheet style report that
analyzes totals in two directions, and uses some common formatting conventions
for financial reports. The report produced is a cross-tabular report that also
shows details.

To create a report from spreadsheet data:

 1. Create a new report and rename it.

 2. Open the new report.

 3. Select a data source for your report. Most spreadsheet software can export
    data in DBF format. Select the DBF file by dragging and dropping it onto an
    open report, or by using the Select a Table or Query window (for more
    information, see Finding data sources for your report). If your spreadsheet
    software cannot produce a DBF file, export the data as a DIF or TXT file.

    The Select Columns and Define Groups window is displayed.

 4. Select the columns you want shown in your report.

 5. Select any columns you want to control grouping down the report. Do not
    select a column to control grouping across.

 6. Select any total types you want. These will be displayed along the bottom
    of your report.

 7. Select OK to close the Select Columns and Define Groups window.

 8. Add a calculated column to create the totals down the side of your report,
    instead of grouping across.

    To speed up the following steps, deselect Autorefresh report on the View
    menu.

    a) Select from the tool bar.

    b) In the Calculate Columns window, type a name for the totals column in
       Result name.

    c) Type an expression to calculate the totals you want in the Expression
       field. For example, to create a total that adds up the amounts in all
       columns for each row, type the expression:

       Column1+Column2+Column3

       where ColumnN is the name of each column.

       The totals do not have to be simple additions. They can be the result of
       any calculation for which you can create an expression.

    d) Select OK to close the Calculate Columns window.

    e) Select any total types you want for the calculated column. These will be
       shown beneath the calculated column, as grand totals, in the report.

    If you deselected Autorefresh report, select it from the View menu to see
    your completed report.


ΓòÉΓòÉΓòÉ 13.7.1. Changing the way numbers are displayed ΓòÉΓòÉΓòÉ

When the figures in your report represent sums of money, you can show this by
adding a currency symbol and by displaying the amounts in a standard format.

To display amounts in your report to two decimal places, with a currency symbol
and a thousands separator:

 1. Double-click on the column you want to change. (You can also use the
    Numeric display format push button on the Column Totals Settings window).

 2. In the Column Settings window, select the Format push button.

 3. In the Format window, select Numeric in the Type list.

 4. In Numeric format, select Currency symbol and 1000`s separator.

 5. Select 2 in the Decimal places list.

 6. Select OK.


ΓòÉΓòÉΓòÉ 13.7.2. Separating totals ΓòÉΓòÉΓòÉ

Often a total in a financial report is separated from other parts of the report
by lines ruled above or below the amount. To separate a total in this way:

 1. Select from the tool bar.

 2. Select the Settings push button for Groups down.

 3. Select the Settings push button for Group total area.

 4. In the Group Total Area Settings window, select the group for which you
    want to highlight the total.

 5. Select the Separator before and Separator after check boxes.

 6. Select the line style you want above the total in Separator before.

 7. Select the line style you want below the total in Separator after.

 8. Select OK.

 9. Select OK to close the Report Settings window.


ΓòÉΓòÉΓòÉ 13.7.3. Printing a wide report on standard paper ΓòÉΓòÉΓòÉ

Financial reports, such as those produced from spreadsheets, typically contain
more columns than fit across a standard page. By printing in landscape format,
in a small font, you can maximize the amount of your report that can be printed
on a single page.

To select landscape orientation:

 1. Select from the tool bar. The Page Settings window is displayed.

 2. Select the paper size you want to use from the Paper list.

 3. Select Landscape in Orientation.

 4. Select OK.

The report is redrawn to show how it will look when printed. If you want to
squeeze more columns onto the page, select from the tool bar to reduce the size
of the font. Each time you press this push button, the font size is reduced by
approximately 20%.

When you are ready to print your report:

 1. Select Print from the Report menu.

 2. In the Print Settings window, in Pagination, select Use current pagination.
    This ensures that any margin restrictions imposed by your printer hardware
    do not cause the report to be repaginated.

 3. Select OK.

Continue printing your report as described in Describing a group.


ΓòÉΓòÉΓòÉ 13.8. Customizing a report ΓòÉΓòÉΓòÉ

To change any report from the usual tabular layout to a layout of your own
design:

 1. Select Customize from the View menu.

 2. Drag the blocks representing columns and column headings to their new
    positions.

 3. Select OK.


ΓòÉΓòÉΓòÉ 14. Defining and working with Visualizer tables ΓòÉΓòÉΓòÉ

Use the Visualizer Table object to organize, access, and maintain data other
than SQL data. You can use Visualizer tables to create and manipulate data if
you are not connected to a database or have QUERY FOR OS/2 installed on a
portable machine. You can also use Visualizer Table if you want to import data
of a different format from another system or export (convert data to a
different format) to another system.

To organize, access, and maintain SQL data, use the SQL Table and View objects.

You can drag and drop a Visualizer table onto the following objects:

o SQL Database (to create an SQL table)
o Visualizer query
o Visualizer report
o Folder
o Printer
o Shredder


ΓòÉΓòÉΓòÉ 14.1. Defining a table ΓòÉΓòÉΓòÉ

A table consists of columns and rows. A column contains data of a particular
category, for example, the names, or salaries of employees. A row contains
related information, for example, the name and salary of an individual
employee.

A fundamental element of a Visualizer table is the key column. Key columns are
used to identify and order each row in your table. A key column when combined
with other key columns, provides a unique identifier or key value for each row
in a table. Each row must have a unique key value. Each Visualizer table must
have at least one key column defined. For information about how to define key
columns and how they are used, see Defining column settings and Ordering rows

You can define your table to suit the type of data you want it to contain by:

o Adding columns
o Renaming columns
o Adjusting column widths
o Adding rows
o Inserting data

To open a new table, drag the Table template from the Shadows of Templates
folder to the desktop.


ΓòÉΓòÉΓòÉ 14.1.1. Adding new columns ΓòÉΓòÉΓòÉ

To add more columns to your table:

 1. Select the column handle for the empty column, which has the default name
    of Column. The column is highlighted to show that it is selected.

 2. Select from the tool bar.

    A new column, Column1, is added to your table. It is placed to the right of
    the selected column.

 3. Select from the tool bar or press Ctrl+A to add a column.

    A new column, Column2, is added to your table. It is placed to the right of
    the original column.


ΓòÉΓòÉΓòÉ 14.1.2. Renaming the columns ΓòÉΓòÉΓòÉ

A column name can be up to 20 characters long. It can contain uppercase and
lowercase alphabetic characters, the numbers 0 through 9, and the underscore
character. It must start with an alphabetic character. It cannot contain
spaces. Examples of valid column names are:

Division_1
ClientName
DeptCode

To rename columns:

 1. Select the column title.
 2. Type the new column title over the existing column title.
 3. Press the Tab key to select the column to the right of the currently
    selected column.
 4. Type the new column title over the existing column title.


ΓòÉΓòÉΓòÉ 14.1.3. Adjusting column widths ΓòÉΓòÉΓòÉ

You can change the width of a column by moving its column separators. To change
the width of the column to the left of a separator by selecting the separator
with mouse button 1 and dragging it using the mouse. Similarly, you can change
the width of the column to the right of the separator by selecting the
separator with Ctrl and mouse button 1.

For more information about creating the same table structure regularly by
copying from a customized template, see Common OS/2 tasks.


ΓòÉΓòÉΓòÉ 14.1.4. Adding new rows ΓòÉΓòÉΓòÉ

To add new rows to your table:

 1. Select the first blank field in the leftmost column and type your data.

 2. Tab to the next field and type your data.

    Repeat this procedure until you have inserted your data into the first
    blank field of the columns that require data. This constitutes a row of
    related information.

 3. Press Ctrl+Enter to add a new row to your table.

    You can insert a new row in any position in your table by placing the
    cursor in the row above the position at which you want to insert the new
    row, and pressing Ctrl+Enter.

 4. Select Save from the Table menu to save your newly defined table and any
    data you have entered.

 5. To close your table, double-click on the title-bar icon.


ΓòÉΓòÉΓòÉ 14.2. Inserting data into your table ΓòÉΓòÉΓòÉ

When a table is opened it is in browse mode by default. To make any changes to
data in the table you must switch to update mode. To switch browse mode to
update mode, select from the tool bar.

There are several different ways of inserting data in your table:

To view and change data quickly:
                    Select from the tool bar, to update several rows of your
                    table at the same time. Press the Tab key to move from cell
                    to cell.

To enter a lot of data quickly, one row at a time:
                    Select Open power insert from the Table menu. The Power
                    Insert window enables you to see many column fields at the
                    same time and to move quickly among them. This is
                    especially helpful if your table has a large number of
                    columns. More detailed information is available in the
                    online help text.

To view one row of data in detail:
                    Select from the tool bar. If you supplied Valid ranges for
                    any of your columns, the form view shows you which values
                    can be accepted in those columns. In addition, the form
                    view indicates the key columns in your table by underlining
                    their column names. For more information, see Checking the
                    data in your table and Displaying your table.

To change data in a table:

 1. Select to place the data in the table in update mode.
 2. Select the cell you want to change.
 3. Type a new value over the existing value.

To delete a row from a table:

 1. Select the handle for the row you want to delete.

    The whole row is highlighted to show that it is selected. This is different
    from step 2 in which you selected a single cell.

 2. Select Delete from the Edit menu. The row is deleted from the table.

 3. To save the changes you have made to the table, select Save from the Table
    menu.


ΓòÉΓòÉΓòÉ 14.3. Displaying your table ΓòÉΓòÉΓòÉ

You can display the data in your table in different ways by:

o Displaying your data as a form
o Anchoring columns in your table
o Selecting specific columns to display
o Displaying valid column values

You can select specific columns of data for display. You can also anchor
columns in your table so that they will always be visible, and not scrolled out
of sight. Alternatively, you can view your table one row at a time, to show the
valid values for each column.


ΓòÉΓòÉΓòÉ 14.3.1. Displaying the table as a form ΓòÉΓòÉΓòÉ

To display your table as a form, select from the tool bar.

Your table is displayed one row at a time, as a form. Each column value for the
row is arranged on your screen as a field in the form. If your table has many
columns, the form will have several pages. You can page through the form by
using the scroll bar. Scroll through the rows in your table by using the
Previous and Next push buttons.

The way each row of data is displayed in a form view, depends on the type of
data each column contains. For information about how column values are
displayed, see Displaying column values

More detailed information about using the form view is available in the online
help text.

To return to the table view, select from the tool bar.


ΓòÉΓòÉΓòÉ 14.3.2. Anchoring columns in your table ΓòÉΓòÉΓòÉ

To anchor specific columns in your table so they remain visible within the
window and not scrolled out of sight:

 1. Select from the tool bar.

 2. From the list of columns, select the columns you want to anchor in your
    table.

 3. Select OK.

The columns you specify are anchored on the left of your table. They are not
scrolled out of sight when you scroll across the table.


ΓòÉΓòÉΓòÉ 14.3.3. Displaying a subset of columns ΓòÉΓòÉΓòÉ

To display specific columns from your table:

 1. Open the table.

 2. To specify the columns you want to display select the Subset columns push
    button in the information area at the bottom of the table.

    If the information area is not displayed, select Subset columns from the
    View menu.

 3. From the list of columns, select the columns you want to display.

    The columns are displayed in the order in which you select them. The
    display order is shown as a number against the column name as you select
    each column.

 4. Select OK.

    Only the columns you select are displayed in your table. The remaining
    columns are still in your table though they are not shown. To display the
    columns in the original order, select Deselect all in the Subset Columns
    window, and then select OK.


ΓòÉΓòÉΓòÉ 14.3.4. Displaying column values ΓòÉΓòÉΓòÉ

Column values in your table can be displayed in several different ways:

Check boxes

o Valid values of 0 or 1 only
o Reject or Replace with default selected for Null values in the Default view
  of the Column Settings window
o Enforce data validation selected in the Column Settings window

You can enter or change data in the column by selecting or deselecting its
check box. When you select the check box, the value 1 is displayed in the
column. When you deselect the check box, 0 is displayed in the column.

You can choose not to have check boxes, by deselecting Use check boxes in the
Table Settings window.

Radio buttons

o Four or fewer valid values. For example, if you specify a Valid range of
  2;13;24, only the values 2, 13, and 24 are valid.
o Reject or Replace with default selected for Null values in the Default view
  of the Column Settings window.
o Enforce data validation selected in the Column Settings window.

You can enter or change data in the column by selecting a radio button. The
value entered in the column corresponds to the value associated with radio
button you select.

Drop-down lists

o More than four valid values
o Four or fewer valid values, but in which null values are allowed
o Four or fewer valid values, but in which Enforce data validation is not
  selected

Single-line entry areas

o No Valid range specified.
o An unknown number of valid values. For example, if you enter a Valid range of
  2:4, any value between 2 and 4 is valid (such as 2.3 or 3.1).

To turn on the checks and defaults for a column, select Enforce data validation
in the Column Settings window.

An information area is displayed at the bottom of your form. This tells you the
number of:

o Columns in your table.
o Columns currently available for display.
o Rows in your table.
o Row currently being displayed
You can select the push buttons in the information area to display the Subset
Columns and Subset Rows windows.

You can choose not to display the information area by deselecting Information
area from the View menu.


ΓòÉΓòÉΓòÉ 14.4. Changing the structure of your table ΓòÉΓòÉΓòÉ

You can change the structure of your table by joining and splitting columns.
You can also change the table by deleting rows and removing empty space.

The following section assumes you are starting from a Visualizer Table window
displaying the current table. It also assumes that there are no column and row
subsets selected.


ΓòÉΓòÉΓòÉ 14.4.1. Joining columns ΓòÉΓòÉΓòÉ

To join two columns and rename the new column:

 1. Hold down the Ctrl key, and select the column handles for the two columns
    you want to join.

 2. Select from the tool bar.

    The name of the first column is displayed in the 1st column field, and the
    name of the second column in the 2nd column field.

 3. Type a hyphen in the Separator field.

 4. Select Join.

    The two columns are joined. The values from the columns are separated by a
    hyphen in the new column. The name of the new column is the same as that in
    the 1st column field.

    You cannot join key columns. You can change the key column value in the
    Column Settings window.

 5. Rename the column by typing a new name over the displayed name.


ΓòÉΓòÉΓòÉ 14.4.2. Splitting a column ΓòÉΓòÉΓòÉ

To split one column into two:

 1. Select the column to be split.

 2. Select from the tool bar.

 3. If the column contains a separator character select the Separator button
    and type the character.

 4. Select Split.

    The column is split into two columns at the separator.

    If your data does not have a separator character, you can specify a
    character position at which the column is to be split. If you split a key
    column, both columns remain as key columns. If your table already has the
    maximum 16 key columns, the existing key column 16 ceases to be a key
    column. More detailed information is available in the online help text.

 5. Rename the columns by typing new names over the displayed names.


ΓòÉΓòÉΓòÉ 14.4.3. Deleting parts of your table ΓòÉΓòÉΓòÉ

You can delete columns, rows, and cells from your table using the handles in
the table and selecting Delete from the Edit menu. The following description
shows how to delete a row.

 1. Scroll down your table to the row you want to delete, or select Find from
    the Edit menu to search for a value. More detailed information is available
    in the online help text.

 2. Select the handle for the row to be deleted.

 3. Select Delete from the Edit menu.
The row is deleted from your table.

If you accidentally delete a row, you can restore it by selecting Undo delete
from the Edit menu. Undo delete restores only the last deletion.


ΓòÉΓòÉΓòÉ 14.4.4. Removing empty space in your table ΓòÉΓòÉΓòÉ

When you delete a large amount of data from a table, its overall size does not
change, because the extra space remains allocated. To free that extra space,
select Save compressed from the Table menu. This saves the table and frees the
extra space.


ΓòÉΓòÉΓòÉ 14.5. Checking the data in your table ΓòÉΓòÉΓòÉ

You can define the type of data or the values that a column can contain using
column settings. Use column settings to define:

o The type of numeric data
o The range of the data (must be a positive value)
o Single values (for example, P for personal friend or B for a business
  associate)

This section shows you how to complete the following tasks:

o Adding another column to an existing table.
o Defining column settings.
o Validating data by checking the value entered in the column.
o Specifying a default value for a column if no value is entered.
o Changing the order of the rows.
o Assigning key columns.


ΓòÉΓòÉΓòÉ 14.5.1. Adding another column ΓòÉΓòÉΓòÉ

To add a column to an existing table:

 1. Select a column.

 2. Select from the tool bar. This adds a new column to the right of the
    selected column, and copies the settings of the selected column to the new
    column.

 3. Rename the new column by typing over the default heading.


ΓòÉΓòÉΓòÉ 14.5.2. Defining column settings ΓòÉΓòÉΓòÉ

You can specify settings for each column in the Column Settings window. In this
window, you can select:

General   To specify the common properties for the column. For example, you can
          select the type of data a column can contain: Character, Numeric,
          Date, or Time. Detailed information is available in the online help
          text.

Default   To enter default values for the column. You can enter a default value
          and specify how to treat blank (or null) values.

Checks    To specify checks on data in the column. For example, you can choose
          to accept data only within a specified range.

Format    To specify how the data in numeric columns is displayed in your
          table. For example, you can select the number of decimal places that
          are displayed.

Fields in the Format view of the Column Settings window can be selected only if
the column contains numeric data. Therefore, when you are using the Format view
of the Column Settings window, you can always tell whether the column holds
numeric data, because the fields are grayed if the data is not numeric.

To change some of the column settings for a column:

 1. Double-click on a column handle. The general settings for the column are
    displayed in the Column Settings window.

    If the column was created as described in the previous section, the
    settings have been copied from the column on which you based the new
    column. The key column value was not copied.

 2. In the Data type field, specify the kind of data the column can contain.

 3. If the column is not a key column, type 0 in the Key field. If the column
    is a key column, type its position in the Key field.

 4. Select Descending to display the key columns in reverse order.

 5. Type the number of characters to be displayed in the Display width field.

    The width (in characters) defined in Display width is the number of
    characters of the column that can be viewed. The display width only limits
    what you can view. It does not affect the amount of data in the column.

 6. Specify where the column will appear in the table in the Position field.
    For example, specifying 2 means the column will appear as the second column
    from the left in the table.

 7. Type a description of the column in the Comment field.

To see the comment about a column, press F1 with the cursor in the column value
entry field.

If you want several of your columns to have the same settings, specify the
settings for the first column and then copy it. The settings, except the Key
value, are also copied. This is quicker than copying a column and then choosing
the settings for each column individually.


ΓòÉΓòÉΓòÉ 14.5.3. Validating data ΓòÉΓòÉΓòÉ

To enter a valid range for the column, so that only certain values can be
accepted in the column:

 1. Select Checks to display the Checks settings in the Column Settings window.

 2. Type the allowable values in the Valid ranges field.

    Entering values other than those specified causes an error message to be
    displayed.

    Choosing a valid range is just one type of check you can do in your table.
    You can validate the data entered in a column in the following ways:

   o Choose the type of data a column can contain.

   o Choose whether to accept blank (or null) values in the column. You can
     replace any null values with your chosen default value.

   o Define a check expression, to ensure that the data meets conditions you
     have chosen.

   o Specify a valid range for values in the column, to ensure that data is
     within the range you have chosen.

    For example, to restrict the valid range for a column of salary data to the
    range of $14,000 to $24,000, type: 14000:24000.

    Detailed information about entering check expressions and valid ranges is
    available in the online help text.


ΓòÉΓòÉΓòÉ 14.5.4. Setting defaults ΓòÉΓòÉΓòÉ

To specify a default value for a column, so the value is automatically entered
if you do not supply a value:

 1. Select Default to display the Default settings in the Column Settings
    window.

 2. Type a value in the Default value field. This value will be used if no
    value is entered.

 3. Select Replace with default.

 4. Check that Enforce data validation is selected for the Type column.

    To activate the checks and defaults for a column, select the Enforce data
    validation check box in the Column Settings window. Enforce data validation
    is automatically selected if you make column into a key column.

 5. Select OK.


ΓòÉΓòÉΓòÉ 14.5.5. Ordering rows ΓòÉΓòÉΓòÉ

To order rows in a table:

 1. In the Column Settings window, select 1 as the key number for the column.
    You must have specified a key column 1 before you can specify a key column
    2, and so on.

    You can check which are the key columns in your table by looking in the
    Table Settings window.

 2. Select OK.

    The rows are reordered when you specify a new key column 1. Rows are
    ordered first by the data type in key column 1, for example, first name
    then second name.


ΓòÉΓòÉΓòÉ 14.6. Selecting rows using selection expressions ΓòÉΓòÉΓòÉ

If you have a large amount of data in a table and want to see only those rows
that hold the information you are interested in, you can create a new table
containing this information.

You can obtain and display this information by using selection expressions.
There are two ways of selecting rows of data using selection expressions,
either by typing an expression directly, or by building one.


ΓòÉΓòÉΓòÉ 14.6.1. Typing an expression directly ΓòÉΓòÉΓòÉ

You can select rows of data by typing a selection expression directly if you
know the correct syntax.

To display a subset of rows in a table:

 1. Select Subset rows from the information area at the bottom of the table.
    The Subset Rows window is displayed.

 2. Type the selection expression directly into the entry field.

 3. Select OK.

The table now displays only the rows selected by the expression. The
information area displays how many rows of the table are currently selected.


ΓòÉΓòÉΓòÉ 14.6.2. Building an expression ΓòÉΓòÉΓòÉ

To ensure the syntax of a selection expression is correct, build the expression
using the Expression Builder. An expression is built using the Expression
Builder by selecting choices from the lists and by typing values into the
Expression (in ASL syntax) field.

The following steps show how to build the expression

 1. Select in the Subset Rows window. The Expression Builder window is
    displayed.

 2. Select Division from the Operands and operators list.

    Division is displayed in the Expression (in ASL syntax) field.

 3. Select Comparison operators from Operands and operator types.

 4. Select = Equal to from the Operands and operators list.

 5. In the Expression (in ASL syntax) field, type "Head Office". after the =
    sign.

 6. Select Logic symbols from Operands and operator types.

 7. Select | Or from the Operands and operators list.

 8. Select Column names from Operands and operator types.

 9. Repeat steps 2 through 5 to add the Research division. Alternatively, you
    can type the rest of the expression directly into the Expression (in ASL
    syntax) field.

10. Select OK in the Expression Builder window.

11. Select OK in the Subset Rows window.

To create a table that holds only the information selected by the expression,
select Copy to from the Table menu.


ΓòÉΓòÉΓòÉ 14.7. Printing a table ΓòÉΓòÉΓòÉ

To print your table, drag it and drop it on a printer object. You can drag
either the icon of a closed table, or the title-bar mini-icon of an open table.

Alternatively you can:

 1. Select Print from the Table menu.

    If you have deselected Review printing options in the QUERY FOR OS/2
    Profile notebook, your table is now printed using the OS/2 default printer.
    If Review printing options is selected (this is the default setting), the
    Printer Selection window is displayed.

 2. Select the printer you want to use.

 3. Select Print and the Print window is displayed. If you are displaying a
    subset of your table you can choose whether to print the subset or the
    whole table. You can also define the width (in characters) and the depth
    (in lines) of the printed page.

 4. Select Print and the Print Copies window is displayed.

 5. Type the number of copies you want to print.

 6. Select Print and the Font Selection window is displayed.

 7. Select a font.

 8. Select Print.

Your table is then sent to the selected printer.


ΓòÉΓòÉΓòÉ 14.8. Importing and exporting tables ΓòÉΓòÉΓòÉ

You can bring in a table from another system and keep a copy of the table. This
is called importing a table.

You can also send a Visualizer table to another system or convert it to another
format for use in other software products. This is called exporting a table.

You can import and export data from many different sources, including:

o Queries

o SQL databases

o Visualizer tables

o AS/400* files (including SQL/400* tables and views)

o Flat (ASCII), List (ASCII CSV), IXF, PCIXF, DBF, DIF Standard, and
  DIFExtended files

Detailed information about the sources you can access is available in the
online help text for the Import and Export windows.

When you export an SQL table to a Visualizer table, the data is stored
differently. Installing and Supporting Visualizer Query for OS/2 describes the
differences.


ΓòÉΓòÉΓòÉ 14.8.1. Importing a table ΓòÉΓòÉΓòÉ

To import a table, starting from a Table window displaying the current table:

 1. Select Import from the Table menu.

    A message appears, warning you that the structure and contents of the
    current table will be overwritten.

 2. Select OK. The Import window is displayed:

 3. Select the type of table you want to import, from the list of Types.

 4. Select the location of the table from the list in the Locations field. When
    you select a location from the list in the Locations field, the names of
    the tables that exist for that location will be listed in the Names field.

 5. Select the name of the table from the list in the Names field.

    To import a subset of the selected table:

    a) Select the Subset push button. The Subset window is displayed.

    b) To select the rows you require type a selection expression in the
       Selection Expression field.

    c) From the list of columns in the Select columns field, select the columns
       you require.

    d) Select OK in the Subset window.

 6. Select OK in the Import window.

    The table or subset you chose is imported into the current table.


ΓòÉΓòÉΓòÉ 14.8.2. Exporting a table ΓòÉΓòÉΓòÉ

Exporting a table is done in a similar manner to that of importing a table. To
export a table, starting from a Table window displaying the current table:

 1. Select Export from the Table menu.

 2. Next to Export as, select the type of destination table or file.

 3. Select the name and, if appropriate, the location of the table or file.

 4. Select Export.

The structure and contents of the current table are copied to the destination
table, or file you have chosen. Your current table is unchanged.


ΓòÉΓòÉΓòÉ 15. Accessing SQL databases ΓòÉΓòÉΓòÉ

Use the SQL Database object to access databases that contain the tables and
views you want to work with. To access a database, associate it with an SQL
Database object, then open the object and work with your data. You can create
several objects that access a single database, each including a different set
of data. When you install QUERY FOR OS/2, it automatically creates an SQL
Database for each database you had access to before installing QUERY FOR OS/2.

Using the SQL Database object, you can retrieve data from local databases (on
your workstation with DB2/2 installed) or remote databases (on another
workstation or host system) in the following database managers:

DB2/2                                             DB2/VM (SQL/DS)
DB2/6000                                          DB2/VSE (SQL/DS)
DB2 on MVS                                        AS/400 Database

You can drag and drop an SQL Database object onto the following objects:

o Folder
o Shredder

This chapter shows you how to:

o Associate an SQL Database object with a database
o Include specific tables and views in the object
o Add more databases that you can associate with an object
o Run SQL statements against a selected database
o Use the results of an SQL statement
o Access an SQL Database object from an application.


ΓòÉΓòÉΓòÉ 15.1. Associating an SQL Database object with a database ΓòÉΓòÉΓòÉ

Before you can use QUERY FOR OS/2 to access SQL tables and SQL views, you must
access the database they are stored in. To access the database, associate an
SQL Database object with it:

 1. Create a new SQL Database object and rename it. Creating a new object lets
    you associate it with a database without changing the original template.
    See Common OS/2 tasks for information on how to do this.

 2. Open the new object's notebook.

 3. On the Database page, select a database from the Database list. If the list
    does not contain the database you want, see Updating the Database list.

 4. Select Apply. The object remains associated with the database until you
    delete the object.

When you open an SQL Database object and select a table, the table is displayed
so you can browse or update it. If you want to create new tables or views, see
Looking at and creating SQL tables or Looking at and creating SQL views.

You must be authorized to access the database. Additionally, before you can
open an SQL Database object, QUERY FOR OS/2 must bind with the database. The
bind process stores files in the database that QUERY FOR OS/2 uses to access
the data. If you are already authorized, QUERY FOR OS/2 automatically binds the
database for you. If you are not authorized, or an error occurs when you open
an object, contact your database administrator.


ΓòÉΓòÉΓòÉ 15.2. Including tables and views in an SQL Database object ΓòÉΓòÉΓòÉ

You can create several objects that access a single database, each including a
different set of data. For example, suppose your business owns 20 stores. The
sales, profit, and inventory data for these stores are in one database. You can
create an object for each store that contains only sales data. Each object
accesses the same database, but shows you only the data you need:

When you first open an SQL Database object after associating it with a
database, the object contains only the existing tables and views you currently
own under your user ID. You can change what is included in the open views of
the object using the Include page of the notebook:

 1. Open the notebook.

 2. On the Include page, type the collection name of what you want to include
    in Collection. The collection name is the first part of a two-part
    identifier. It can be the user ID of the owner of the tables and views or
    some other name, depending on the database you are accessing.

    You can use an asterisk (*) or a question mark (?) as a global character to
    specify more than one table or view. A global character can represent other
    characters in a name. For example, j*n includes tables owned by Joan, John,
    and Jan, while j?n includes only Jan's tables.

    If you leave Collection blank, it defaults to the current user ID. If you
    created tables or views and assigned them a collection name other than your
    current ID, they are not included.

 3. Type the second part of the two-part identifier in the Name field.

 4. Select SQL Table, SQL View, or both from the Type list box.

 5. With Automatic refresh selected, the contents of the object are refreshed
    when you move the cursor out of the field or close the window. The object
    now includes the tables and views that meet the new settings. If you
    deselect Automatic refresh and change the settings, you must explicitly
    refresh the database object by selecting Refresh now from the object's
    pop-up menu.


ΓòÉΓòÉΓòÉ 15.3. Updating the Database list ΓòÉΓòÉΓòÉ

The Database page of the notebook contains the Database list. You can associate
an SQL Database object with any database in the list. The list contains all the
databases that are cataloged in DB2/2 or CAE/2. Cataloging a database adds it
to the database directory, which is a file that contains information about the
databases, for use by DB2/2 or CAE/2. When you install QUERY FOR OS/2, it
automatically creates an SQL Database object for each cataloged database and
adds it to the list.

If the database you want is not listed, you can add it to the list by
cataloging it. Another way to add a database to the list is by creating a local
database, which catalogs it automatically.


ΓòÉΓòÉΓòÉ 15.3.1. Cataloging a database ΓòÉΓòÉΓòÉ

To catalog a database:

 1. Create a new SQL Database object and rename it. See Common OS/2 tasks for
    information on how to do this.

 2. Open the new object's notebook.

 3. On the Database page, select New.

 4. In the New Database window, select Catalog a database.

 5. Type an alias for the database you want to catalog in Alias. An alias is an
    alternative name that must be unique on your workstation.

 6. Type the name of the database in Database. The name was assigned when the
    database was created. Contact your database administrator for a list of
    databases you can catalog.

 7. Type a descriptive word or phrase in Comment to help you remember what you
    are using the database for. This step is optional.

 8. If the database is remote (on another workstation or a host system):

    a) Select Remote.
    b) From Workstation, select the name of the workstation on which the
       database is stored.

    If the database is local (on your workstation):

    a) Select Local.
    b) From Drive, select the drive on which the database is stored.

 9. Select Catalog. The database is added to the Database list on the Database
    page of the notebook. You can now access it with the current object; see
    Associating an SQL Database object with a database.

The other way to add a database to the Database list is to create a local
database.


ΓòÉΓòÉΓòÉ 15.3.2. Uncataloging a database ΓòÉΓòÉΓòÉ

On the Database page of the notebook, the Database list contains all the
databases that are cataloged in DB2/2 or CAE/2 and that you are authorized to
access. To remove a database from the list you must uncatalog it, which removes
it from the database directory. Removing a database from the list does not
delete it.

To uncatalog a database:

 1. Open the Visualizer folder.

 2. Open the Shadows of Templates folder.

 3. Open the notebook of the SQL Database template.

 4. On the Database page, select a database from the Database list.

 5. Select Uncatalog.

 6. A confirmation window is displayed. Select Yes to uncatalog the database.


ΓòÉΓòÉΓòÉ 15.3.3. Creating a local database ΓòÉΓòÉΓòÉ

You can use the SQL Database object to create a local DB2/2 database on your
workstation. Creating a database in this way also catalogs it, which adds it to
the DB2/2 or CAE/2 database directory. After creating a local database, you can
associate it with the current object.

To create a local database:

 1. Create a new SQL Database object and rename it. See for information on how
    to do this.

 2. Open the new object's notebook.

 3. On the Database page, select New.

 4. In the New Database window, select Create a local database.

 5. Type a name for the database in Database. The name must be unique on your
    workstation. For information about naming conventions, see the SQL
    reference for the database you are using.

 6. Type a descriptive word or phrase in Comment to help you remember what you
    are using the database for. This step is optional.

 7. From Drive, select the drive on which the database is to be stored.

 8. Select Create to create the database.

When a local database is created, it is automatically cataloged in the DB2/2 or
CAE/2 database directory and added to the Database list on the Database page of
the notebook. You can now access it through the current object; see Associating
an SQL Database object with a database.


ΓòÉΓòÉΓòÉ 15.3.4. Shredding a database ΓòÉΓòÉΓòÉ

When you delete an SQL Database object, you delete only the object, not the
database it accesses. There is one exception. If the object accesses a local
database, you can delete the database as well.

To delete a database:

 1. Close all open views (notebook, icon view, details view, and Enter SQL
    window) of the object.

 2. Drag the object to the shredder or select Delete from the pop-up menu.

 3. In the Delete Objects window, select Delete. If Confirm on folder delete is
    selected, a prompt is displayed; select Yes. The SQL Database object is
    deleted, although the actual database still exists.

 4. If the database is local, another message is displayed. Select Yes to
    delete the local database.

You cannot delete remote databases with an SQL Database object. To delete a
remote database, contact your database administrator or the owner of the
database.


ΓòÉΓòÉΓòÉ 15.4. Running SQL statements ΓòÉΓòÉΓòÉ

QUERY FOR OS/2 offers two ways to run SQL statements:

o If you run a certain SQL statement frequently, or you want to store a
  statement and use its result in a report, use an SQL statement object. Use an
  SQL statement object when:

   - You use the same statement frequently
   - You want to use the result of a SELECT statement to create a report
   - You are importing a statement from a plain text file
   - You are importing a Prompted Query or an SQL Query from Query Manager

  See Creating effective SQL statements for more information.

o If you are working with the tables and views in an SQL Database object, you
  can use the Enter SQL window to run statements in that database, as described
  in this chapter. Use the Enter SQL window for running:

   - Statements you run occasionally

   - Database administration statements

   - Multiple SQL statements

   - Statements with active cursors, with which you can retrieve a set of rows
     from a table.

  You can run any statement except CONNECT from the Enter SQL window.

To run an SQL statement:

 1. Open the Enter SQL window:

    a) Display the object's pop-up menu.
    b) Select the arrow to the right of Open.
    c) Select the Enter SQL choice.

 2. Type a statement in the SQL statement field.

 3. Select Submit to send the statement to the database.

    With Automatic commit selected, the database runs the statement. If the
    statement results in any changes to the database, the changes are added
    automatically.

    If you deselect Automatic commit, you must select either Commit, which adds
    the changes to the database, or Rollback, which directs the database to
    disregard the results of the statement. You can commit or roll back several
    submitted statements at a time. For more information about committing and
    rolling back statements, see the documentation for the database you are
    using.

Any statement you submitted during a session is available to you with Retrieve.
Retrieve redisplays your statements in the SQL statement field, where you can
edit them and submit them again. Cancel SQL interrupts the processing of
statements running in DB2/2 and DB2/6000.

During a session, you can submit multiple statements before committing or
rolling them back. Separate the statements with a semicolon (;). The Statements
pending indicator shows how many statements you submitted, but not committed,
to the database.


ΓòÉΓòÉΓòÉ 15.4.1. Editing statements ΓòÉΓòÉΓòÉ

You can use the OS/2 clipboard to copy and paste text in an SQL statement. For
example, to copy an item to the OS/2 clipboard, select it and press Ctrl+Ins.
To paste an item from the OS/2 clipboard, move the cursor to where you want the
item to appear in the SQL statement field and press Shift+Ins.

The SQL History window stores statements you submit during a session. From the
SQL History window you can run or copy statements to the clipboard or to the
Enter SQL window. See Using the results of an SQL statement.


ΓòÉΓòÉΓòÉ 15.4.2. Adding comments ΓòÉΓòÉΓòÉ

You can add comments to an SQL statement. Comments help you and others
understand what the statement does. Each comment must begin with two dashes
(--). You can type comments alone or at the end of any line of an SQL
statement, but not embedded in a statement. Comments span to the end of the
line. For example:

-- This query lists employee name,
-- years of employment, and salary

SELECT NAME, YEARS, SALARY   -- column names
  FROM STAFF                 -- table name
  ORDER BY NAME              -- sorted alphabetically

The comments are displayed only in the SQL statement field. They are not sent
to the database.


ΓòÉΓòÉΓòÉ 15.5. Using the results of an SQL statement ΓòÉΓòÉΓòÉ

After you submit or commit statements, check the Result field in the Enter SQL
window for confirmation of your updates and any messages or return codes from
the database. If you are running a SELECT statement, the results are displayed
in different window.

The statements you submit during this session also appear in the SQL History
window. To display the SQL History window, select the Show history button from
the Enter SQL window:

From the SQL History window you can resubmit statements you already submitted,
copy them to the Enter SQL window to edit and resubmit them, or copy them to
the OS/2 clipboard. The statements remain in the window until you close it.

To submit a statement:

 1. Select one or more statements from those displayed in the window.
 2. Select Submit from the Selected pull-down menu.

To copy a statement to the Enter SQL window:

 1. Select one or more statements from those displayed in the window.
 2. Select Copy to SQL window from the Selected pull-down menu.

To copy a statement to the clipboard:

 1. Select one or more statements from those displayed in the window.
 2. Select Copy from the Edit pull-down menu.

When the database finishes processing an SQL statement, it returns messages and
codes in the Result field of the Enter SQL window.


ΓòÉΓòÉΓòÉ 15.6. Accessing an SQL Database object from your application ΓòÉΓòÉΓòÉ

Use the DBALIAS setup string keyname to access a specific database when
invoking QUERY FOR OS/2 from your application. DBALIAS can set a default
database alias in the SysCreateObject REXX utility object, the wpSetup
Workplace Shell* object instance method, and the WinCreateObject Presentation
Manager* function. The syntax is:

DBALIAS=alias

where the alias of the database is cataloged in the DB2/2 or CAE/2 database
directory. The default value is no alias at all.

See the OS/2 programming documentation for more information about setup string
keynames.

This example shows you how to use DBALIAS. The result is a working REXX EXEC.

/* CREATEDB -- REXX exec to create a Workplace Shell SQL Database object */
/* 1. Copy this sample to a file named CREATEDB.CMD.                     */
/* 2. From an OS/2 Window, change to the directory containing this file  */
/* 3. Type createdb ? for instructions on how to use this command        */
/*-----------------------------------------------------------------------*/

/* Load the REXX SysCreateObject utility function */
call RxFuncAdd `SysCreateObject`, `RexxUtil`, `SysCreateObject`

parse arg title "(" aliasName        /* Get the title and database alias */

if title = `?` | title = `` then     /* Is caller asking for help?       */
  Signal Help

/* Ask Workplace Shell to create a SQLDatabase object on the Desktop.    */
/* If an alias was specified, pass that alias to the object using the    */
/* DBALIAS keyword in the setup string.                                  */
if aliasName = `` then               /* Was a database alias specified?  */
  setupString = ``                   /* ...no, so no special setup       */
else
  setupString = "DBALIAS="aliasName  /* ...yes, build a setup string     */

if SysCreateObject("IBMSQLDatabase",title,"<WP_DESKTOP>", setupString,"Fail") then
  Say `Object created successfully!`
else
  Say `Object creation failed....`
Say `Title=`title `  Location=Desktop   Database Reference=`aliasName
exit 0

Help:
  /* Get the name of this exec */
  parse source . . execPathAndName`.cmd` .
  myName = translate(SubStr(ExecPathAndName, LastPos("\", execPathAndName) + 1 ))

  say
  say myName "-- REXX exec to create a Workplace Shell SQL Database object"
  say
  say "Syntax:"
  say "  "myName "title ( aliasName"
  say
  say "  where:"
  say "    title     = the title to give the new SQL Database object"
  say "    aliasName = the name of the DB2/2 database alias the new object"
  say "                should use as the referenced database.  This alias"
  say "                must already be cataloged to DB2/2."
  say
  Say "Example:"
  say "  "myName "MySampleDatabase (SAMPLE"
  say
  exit 8


ΓòÉΓòÉΓòÉ 16. Improving data integrity, performance, and security ΓòÉΓòÉΓòÉ

SQL databases can contain many tables, each containing data of different
origins and varying security levels. Use an SQL Table object to increase data
integrity and enhance performance. Use SQL Table and SQL View objects to ensure
data security. This chapter shows you how to do this by creating primary keys,
constraints, indexes, and authorizations.

This chapter also contains a general overview of referential integrity,
performance, and privileges. Some of the information in this chapter may vary
for your particular database. You can find specific information about these
concepts in the library for your database.


ΓòÉΓòÉΓòÉ 16.1. Maintaining integrity between tables ΓòÉΓòÉΓòÉ

Suppose your company keeps personnel information in tables in a database. One
table, DEPARTMENT, lists all the departments in the company and the managers of
those departments. Another table, EMPLOYEE, lists all the employees in the
company and the departments that they work in. To ensure that every employee in
the EMPLOYEE table is in a department listed in the DEPARTMENT table, you can
use a concept called referential integrity.


ΓòÉΓòÉΓòÉ 16.1.1. Building relationships between tables ΓòÉΓòÉΓòÉ

You can build relationships between and within tables to ensure that
referential integrity is maintained. You can establish these relationships
using constraints. Constraints ensure that the values in a column or set of
columns in one table (called a primary key) match the values in a column or set
of columns in the same or another table (called matching columns).

Term                          Definition
primary key                   A column, or an ordered collection of columns,
                              whose values uniquely identify a row in a table.
parent table                  The table in a constraint that contains the
                              primary key.
matching columns              A column, or set of columns, in a table
                              containing values that are also contained in the
                              primary key of the parent table. The values in
                              the matching column do not have to be unique, but
                              they must be represented in the primary key.
referenced table              The table in a constraint that contains the
                              matching columns. The parent table and the
                              referenced table can be the same.

In the tables below, the DEPTNO column in the DEPARTMENT table and the WRKDEPT
column in the EMPLOYEE table create a constraint.

ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ  ΓöîΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö¼ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÉ
Γöé DEPTNO  Γöé DEPTNAME   Γöé MGRNO  Γöé  Γöé EMPNO  Γöé LASTNAME  Γöé FIRSTNAME Γöé WRKDEPT  Γöé
Γöé (Primary Γöé       Γöé     Γöé  Γöé     Γöé      Γöé      Γöé (Matching Γöé
Γöé  Key)   Γöé       Γöé     Γöé  Γöé     Γöé      Γöé      Γöé  Column)  Γöé
Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ  Γö£ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö╝ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöñ
Γöé  A00   Γöé Finance   Γöé 000010 Γöé  Γöé 000014 Γöé Haas    Γöé Christine Γöé C01    Γöé
Γöé  B01   Γöé Planning   Γöé 000020 Γöé  Γöé 000027 Γöé Kwan    Γöé Sally   Γöé D11    Γöé
Γöé  C01   Γöé Marketing  Γöé 000030 Γöé  Γöé 000033 Γöé O'Connell Γöé Sean    Γöé A00    Γöé
Γöé  D11   Γöé Development Γöé 000060 Γöé  Γöé 000059 Γöé Nicholls  Γöé Heather  Γöé C01    Γöé
ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ  ΓööΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓö┤ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÿ
DEPARTMENT table (parent table)    EMPLOYEE table (referenced table)


ΓòÉΓòÉΓòÉ 16.1.1.1. Using a primary key ΓòÉΓòÉΓòÉ

A primary key must exist before you create a constraint. A primary key must be
unique. This means that:

o When a primary key is made up of one column in a table, each value in that
  column must be unique (different).

o When a primary key is made up of two or more columns in a table (specified in
  a certain order), the values in each column do not have to be unique, but the
  collection of values representing each row must be unique.


ΓòÉΓòÉΓòÉ 16.1.1.2. Creating a primary key ΓòÉΓòÉΓòÉ

QUERY FOR OS/2 simplifies creating a primary key by determining which columns
in your table can be included in the primary key, and displaying these columns
in the Available Columns list on the Primary Key page of the notebook. You
create the primary key by choosing columns from the list, then selecting Apply
all. You cannot delete a primary key after it is applied to the database.

To create a primary key:

 1. Open the notebook for the table.

 2. On the Primary key page, double-click on each column name in the Available
    Columns list that you want to add to the primary key. As you add columns,
    the column names are removed from the Available Columns list and displayed
    in the Key Columns list.

 3. To delete columns from the primary key, double-click on each column name in
    the Key Columns list that you want to delete.

 4. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.


ΓòÉΓòÉΓòÉ 16.1.2. Maintaining integrity when deleting data ΓòÉΓòÉΓòÉ

You might need to delete rows from a table that is used in a constraint. For
example, if two departments in the company are merged into one, the old
department numbers from the DEPARTMENT table should be deleted and replaced
with the number for the new department. But by deleting the old department
numbers from the DEPARTMENT table, the referential integrity of the EMPLOYEE
table is compromised, because there are now employees assigned to departments
that do not exist.


ΓòÉΓòÉΓòÉ 16.1.2.1. Using delete rules ΓòÉΓòÉΓòÉ

In situations like this, a delete rule is used to maintain the integrity of the
data. The delete rule, which is part of the constraint, determines what happens
to those rows in the referenced table (called dependent rows) whose values
match the value in the row being deleted from the parent table.

The delete rules are:

Restrict  If a value in a dependent row matches the primary key value in the
          row being deleted from the parent table, no rows are deleted from the
          parent table or from the dependent table.

          Using this rule, you could not delete department C01 from the
          DEPARTMENT table because there are dependent rows in the EMPLOYEE
          table that contain the same value. Therefore, both tables would stay
          as they are.

Cascade   All dependent rows with values that match the primary key value in
          the row being deleted from the parent table are deleted, along with
          the row in the parent table.

          Using this rule, all rows in the EMPLOYEE table containing the value
          C01 are deleted if you deleted department C01 from the DEPARTMENT
          table.

Nullify   If a value in a dependent row matches the primary key value in the
          row being deleted from the parent table, the row in the parent table
          is deleted and the value in the dependent row is set to a null value.

          Using this rule, every occurrence of the value C01 in the EMPLOYEE
          table would change to a blank (null) if you deleted department C01
          from the DEPARTMENT table.

The following sections show you how to create and delete constraints.


ΓòÉΓòÉΓòÉ 16.1.2.2. Creating a constraint ΓòÉΓòÉΓòÉ

Before you add a constraint to a table, the following criteria must be met:

o The referenced table must exist.

o The referenced table must have a primary key defined.

o The matching columns must have the same data type and width as the primary
  key columns in the referenced table.

o You must have references authority on the referenced table and alter
  authority on the table containing the matching columns. See your SQL
  reference for more information on references authority and alter authority.

You can delete constraints that are no longer needed. You can also create
additional constraints and view information about existing constraints. You can
modify an existing constraint only by deleting it and then recreating it with
the new information.

To create a new constraint:

 1. Open the notebook.

 2. On the Constraints page, type a name for the constraint in the Name field.

 3. Select a radio button to specify the Delete Rule.

 4. Type the table name and collection name of the Referenced Table, or select
    Table List to search for a referenced table.

 5. Select Add to add the constraint.

 6. If you want to add more constraints, select Clear to clear the fields, then
    repeat steps 2 through 5

 7. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.

The names of the constraints on the table are displayed in the Constraints
list.

 1. Open the notebook.

 2. On the Constraints page, double-click on each constraint in the Constraints
    list that you want to delete.

 3. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.


ΓòÉΓòÉΓòÉ 16.2. Improving data access with indexes ΓòÉΓòÉΓòÉ

When you run a query on a table that does not have an index, the database reads
each row in a table to determine if that row should be retrieved. When you
create an index for a table, the database does not read every row in a table.
Instead, it refers to the index and follows the pointers to retrieve the
necessary rows. This speeds up the time it takes to retrieve the data.

A simple index is basically a two-column list. The first column contains a set
of values, which represent values in the table. This column is referred to as
the index key.

The second column in the index contains pointers from the index key to the rows
in the table where those values exist.

You can create two types of indexes: nonunique or unique. A nonunique index
contains duplicate values, but a unique index does not.

You can include one or more columns in the index key. Columns that are used
frequently when selecting, joining, grouping, or ordering data are good choices
for an index.

You can create more than one index on a table. However, specifying a large
number of columns in an index, or creating many indexes for a table, can
increase database processing time.

You can delete indexes from a table that is applied to the database.

To see if indexes are already defined on the table, open the notebook to the
Indexes page and look at the Indexes list.

To create an index or add a new index to a table:

 1. Open the notebook.

 2. On the Indexes page, type the name of a new index in the Name field.

 3. Select a button under Allow duplicate rows to specify whether several rows
    in the index can contain the same values. Choose Yes to create a nonunique
    index, or No to create a unique index.

 4. Select a column to be included in the index key from the Available Columns
    list.

 5. Select a button under Order to specify the sorting order for the column.
    Select Ascending to order the column from lowest to highest (for example, 1
    to 9) or Descending to order it from highest to lowest (for example, 9 to
    1).

 6. Select Add in the Index Definition area to add the column to the index key.
    When you add a column to the index key, it is removed from the Available
    Columns list and displayed in the Key Columns list.

 7. To add more columns to the index, select Clear on the Indexes page to clear
    the fields, then repeat steps 4, 5, and 6 for each additional column.

 8. Select Add in the Indexes list to add the index. The index name is
    displayed in the Indexes list.

 9. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.

To delete an index:

 1. Open the notebook.

 2. On the Indexes page, select an index name from the Indexes list.

 3. Select Delete.

 4. Select Apply all on the Table page to apply the changes to the database,
    and close the notebook.


ΓòÉΓòÉΓòÉ 16.3. Sharing data with others ΓòÉΓòÉΓòÉ

Often, tables or views are owned by a single person, but needed by many people.
However, not all people need the same access to the data. Some people might
need to update data, while others might need only to look at the data.

You can grant privileges for tables and views that you own, or have grant
authority for. Privileges are different types of accesses for a particular
table or view. Privileges can be given to a single user or a group of users.
Granting a privilege on a specific table or view creates an authorization. You
can also add to and delete authorizations that you previously granted.


ΓòÉΓòÉΓòÉ 16.3.1. Authorizing users ΓòÉΓòÉΓòÉ

You can authorize one, some, or all users in your company to access tables and
views that you own.

To authorize users:

 1. Open the notebook.

 2. On the Authorization page, select Clear to clear the fields.

 3. Type a collection name in the User Name field. The collection name could be
    a user ID or some other identifier of the user or user group. Type PUBLIC
    to authorize users who have access to the database where the table or view
    is stored, or PUBLIC AT ALL LOCATIONS to authorize users on local and
    remote databases.

    PUBLIC AT ALL LOCATIONS is not available on all databases. See your SQL
    reference for more information.

 4. Select the privileges being granted from the Authorizations list.

 5. If the database the view is stored in is DB2 for MVS, you can allow users
    to grant the same privileges they are authorized for to other users.

    Select the privileges the user will be authorized to grant from the Grant
    list. You can authorize the user to grant any privilege specified in step 4

 6. Select Add. The collection name is enclosed in brackets, indicating that it
    is not applied to the database. When the collection name is applied, the
    brackets are removed.

 7. Repeat steps 2 through 6 for each collection name.

 8. Select Apply all on the Table or View page, and close the notebook.


ΓòÉΓòÉΓòÉ 16.3.2. Updating privileges for existing users ΓòÉΓòÉΓòÉ

You can grant additional privileges on a table or view, or you can revoke
privileges.

To update privileges:

 1. Open the notebook.

 2. On the Authorization page, select a collection name from the Authorized
    users list.

 3. In the Authorizations list, select the privileges being granted, or
    deselect the privileges being revoked.

 4. Select Modify.

 5. Select Apply all on the Table or View page to apply the changes to the
    database, and close the notebook.


ΓòÉΓòÉΓòÉ 16.3.3. Deleting users from an authorized group ΓòÉΓòÉΓòÉ

You can revoke all privileges for a user or group of users.

To revoke privileges:

 1. Open the notebook to the Authorization page.

 2. Select a name from the Authorized users list.

 3. Select Delete.

 4. Repeat steps 2 and 3 for each name you want to delete.

 5. After you delete all required collection names, select Apply all on the
    Table or View page to apply the changes to the database, and close the
    notebook.


ΓòÉΓòÉΓòÉ 17. Creating effective SQL statements ΓòÉΓòÉΓòÉ

If you are familiar with SQL, you can define an SQL Statement to retrieve data
from a database, to create or delete a table or view in a database, to prepare
an index on a table, or to grant and revoke privileges on a database. You can
store your SQL Statement and allow other users, who may have no knowledge of
SQL, to run it and use any result produced.

This chapter describes which SQL statements can be used in an SQL Statement
object, when changes are committed to the database, and how to import queries
from other products. It also provides techniques and suggestions for defining
effective SQL statements that you and your co-workers will find easy to use.


ΓòÉΓòÉΓòÉ 17.1. Defining and running SQL statements ΓòÉΓòÉΓòÉ

QUERY FOR OS/2 offers two ways to define and run SQL statements:

o If you are working with the tables and views in an SQL Database object, you
  can use the Enter SQL window in the object to run statements in that
  database. Use the Enter SQL window for defining:

   - Statements you run occasionally

   - Database administration statements

   - Multiple SQL statements

   - Statements with active cursors, with which you can retrieve a set of rows
     from a table.

  Enter SQL can run any statement except CONNECT.

  For further information about the Enter SQL window, see Running SQL
  statements.

o If you run a certain SQL statement frequently, or you want to store a
  statement and use its result in a report, use an SQL Statement object. Use an
  SQL Statement object when:

   - You use the same statement frequently

   - You want to use the result of a SELECT statement to create a report

   - You are importing a statement from a plain text file, such as an SQL query
     exported from QMF or Query Manager/400

   - You are importing a Prompted Query or an SQL query exported from Query
     Manager

To define an SQL Statement object:

 1. Create a new SQL Statement and rename it. See Common OS/2 tasks for
    information on how to do this.

 2. Open the SQL Statement.

 3. Associate the SQL Statement with a database by selecting from the Database
    list.

    The SQL Statement connects to this database when you run it, but the SQL
    Statement is not stored in the database.

 4. Type your SQL statement in the entry field.

    An SQL Statement can contain a single SQL statement with appropriate
    subclauses. Most of the common statements for selecting data, for creating
    and deleting tables and views, for creating indexes, and for granting and
    revoking privileges are supported. See the next section for more detailed
    information on the supported SQL statements.

    You can edit your SQL Statement using the standard keyboard editing keys
    (for example, Home and Delete), or by using the clipboard. Select the Cut,
    Copy or Paste choices from the Edit menu to use the clipboard.

 5. Select the Save choice from the SQL menu.


ΓòÉΓòÉΓòÉ 17.2. Which SQL statements can I use? ΓòÉΓòÉΓòÉ

You can use a single supported SQL statement with appropriate subclauses in an
SQL Statement object. The supported statements are executable SQL statements
that can be dynamically prepared. This includes the most common statements for
the following tasks:

Task                                    Typical supported statements
Creating tables and views               CREATE TABLE
                                        CREATE VIEW
Updating tables and views               INSERT
                                        UPDATE
                                        DELETE
                                        ALTER TABLE
Creating an index                       CREATE INDEX
Selecting data                          SELECT
Deleting tables and views               DROP
Controlling access to data              GRANT
                                        REVOKE
                                        LOCK TABLE

SQL statements that define and control cursors directly and statements that use
host variables are not supported.


ΓòÉΓòÉΓòÉ 17.3. Committing changes ΓòÉΓòÉΓòÉ

Because SQL Statement issues a COMMIT statement after you run it, you should
not use COMMIT or ROLLBACK within an SQL statement.

The SQL Statement template provided with QUERY FOR OS/2 has the push button
selected on the tool bar. An SQL Statement created from this template does not
commit changes to the database until you confirm that you want to do this in
response to a message.

Select from the tool bar to have changes committed to the database as soon as
the SQL Statement is run, without prompting you.


ΓòÉΓòÉΓòÉ 17.4. Making an SQL statement reusable ΓòÉΓòÉΓòÉ

You can make an SQL Statement reusable by using variable values in its
definition. You assign a specific value to the variables when the SQL Statement
is run.

For example, consider the following SELECT statement:

SELECT * FROM CUSTOMER
WHERE STATE = (SELECT STATE FROM CUSTOMER
               WHERE "Customer Name" = `@Customer_name`)

When run, this statement shows you all the customers with addresses in the same
state as the customer whose name you specify.

When you run this SQL Statement, the Value Substitution window is displayed.

Type the customer name of your choice, and select OK. The variable value in the
SELECT statement is replaced with the name you type before the data is
retrieved.


ΓòÉΓòÉΓòÉ 17.4.1. Naming variable values ΓòÉΓòÉΓòÉ

A variable value must consist of an @ character followed by up to 20
characters. The characters must comprise alphanumeric characters or the
underscore character. The first character after the @ cannot be a number.
Spaces cannot be used within the variable. For example:

Valid               Not valid
@var1               @%1
@Customer_number    @Customer number
@Address1           @1Address

You can use any number of variable values anywhere in an SQL statement. For
example, you can use a variable value for a keyword, a table name, or a
selection criterion.


ΓòÉΓòÉΓòÉ 17.4.2. Tips on using variable values ΓòÉΓòÉΓòÉ

Use the following tips to make your variable values user-friendly:

o Use meaningful names for the variable values.

  Variable values are shown next to an entry field in the Value Substitution
  window when the SQL Statement is run. It is much easier to remember what to
  type next to a value such as "Customer_name" than a value such as "var1".

o Provide a cue in the icon name.

  Include a word such as "any" in the icon name to provide a visual reminder
  that your SQL Statement asks the user to type values for its variables. For
  example, Weekly sales comparison - any 2 products.


ΓòÉΓòÉΓòÉ 17.5. Tips on defining SQL statements for use by others ΓòÉΓòÉΓòÉ

When you define SQL Statements for others to run, there are a number of tips
you can follow to simplify both using and maintaining the SQL Statements. Some
of these tips are more obvious than others. The following sections give a brief
summary of them.


ΓòÉΓòÉΓòÉ 17.5.1. Naming icons ΓòÉΓòÉΓòÉ

Help your users identify an SQL Statement by giving the desktop icon a
meaningful name. For example, if the SQL Statement selects sales figures from
tables updated weekly for the ten best-selling products, a good title would be
Top 10 sellers - Last week.

For information on how to change the name of an icon, see Common OS/2 tasks.


ΓòÉΓòÉΓòÉ 17.5.2. Running an object by double-clicking ΓòÉΓòÉΓòÉ

If your users often run an SQL Statement, but rarely or never make changes to
it, you can make life simpler for them by changing the default Open action.
Your users can then run the SQL Statement by double-clicking on its icon.

To change the default Open action:

 1. Display the pop-up menu for the icon.
 2. Select the arrow to the right of Open.
 3. Select Settings. The SQL statement notebook is displayed.
 4. Select the Menu notebook page.
 5. In the Available menus list, select ~Open.
 6. Select the Settings push button. The Menu Settings window is displayed.
 7. In the Default action list, select Run SQL.
 8. Select OK.
 9. Close the SQL statement notebook.

Your users can still open the object in the usual way by using the pop-up menu
for the icon.


ΓòÉΓòÉΓòÉ 17.5.3. Changing the associated database ΓòÉΓòÉΓòÉ

If you create an SQL Statement that can be associated with several databases,
save it with a blank entry in the Database list. Users can then temporarily
associate it with any of their databases.

If you do this, make the SQL Statement read-only, and do not change the default
Open action of the object.


ΓòÉΓòÉΓòÉ 17.5.4. Preventing accidental changes ΓòÉΓòÉΓòÉ

If you want users to be able to change an SQL Statement, but also want to
minimize accidental changes, there are two steps you can take:

o Ensure that when you save the SQL Statement, the push button is selected on
  the tool bar. The SQL Statement cannot then be edited until the push button
  is selected on the tool bar.

o Ensure that Confirm is selected on the SQL menu. Any changes to the database
  are made only when the user confirms them in response to a message.


ΓòÉΓòÉΓòÉ 17.5.5. Preventing all changes ΓòÉΓòÉΓòÉ

If none of your users need to change the SQL Statement, or if several users
access the same icon on a LAN, you can prevent any changes by making the SQL
Statement a read-only file.

To make an SQL Statement a read-only file:

 1. Open the settings notebook for the SQL Statement.
 2. Select the File notebook tab.
 3. Select the right arrow at the bottom of the page to display Page 2 of the
    File section.
 4. In Flags, select Read-only, then close the notebook.

Alternatively, if the SQL Statement is stored on a LAN resource, you can make
the entire resource read-only.


ΓòÉΓòÉΓòÉ 17.6. Importing queries from other products ΓòÉΓòÉΓòÉ

You can import the following queries from other products:

o SQL queries and prompted queries produced by Query Manager

o Plain text SQL queries produced by other query products, for example, QMF or
  Query Manager/400

To import a Query Manager query:

 1. Select Import QM from the SQL menu. The Import window is displayed.

    A list of all the SQL databases cataloged on your workstation is shown in
    the Locations list.

 2. Select a database in the Locations list.

    A list of all collection names associated with the selected database is
    shown in the Locations list. The collection name (sometimes called the
    table prefix) is usually the same as the user ID of the person who created
    the tables and views.

 3. Select a collection name in the Locations list.

    A list of queries identified by the selected collection name is shown in
    the Names list.

 4. Double-click on the query you want in the Names list.

If the query contains lines that are longer than 254 characters, the part of
the line after the 254th character will be wrapped onto the next line. This may
create a statement that is not valid in SQL. However, you can open the
resulting SQL Statement and edit the lines to create a valid statement.

To import a plain text (.TXT) file:

 1. Select Import text from the SQL menu. The Import window is displayed.

 2. Type the name of the file in the Name field, or select it from the Names
    list.

 3. Type the location of the file in the Location field, or select it from the
    Locations list.

 4. Select OK.

If the query contains lines that are longer than 254 characters, the part of
the line after the 254th character will be truncated. Open the new SQL
Statement and retype that part of the line.


ΓòÉΓòÉΓòÉ 17.7. Saving an SQL Statement ΓòÉΓòÉΓòÉ

To save an SQL Statement on the OS/2 file system, select Save on the SQL menu.

You cannot select Save if you do not have write access to the SQL Statement
that you have opened. However, you can select Copy to from the SQL menu and
save the SQL Statement using a different file name or location.


ΓòÉΓòÉΓòÉ 17.8. Printing an SQL Statement ΓòÉΓòÉΓòÉ

To print an SQL Statement, drag it and drop it on a printer object. You can
drag either the icon of a closed SQL Statement, or the title-bar small icon of
an open SQL Statement.


ΓòÉΓòÉΓòÉ 18. Customizing Query for OS/2 objects ΓòÉΓòÉΓòÉ

Use the Profile notebook to customize settings used by all QUERY FOR OS/2
objects. The notebook includes sections called Visualizer, SQL, Window, and
General. The Window and General sections are the same as all other OS/2
objects; refer to OS/2 online help or documentation for more information about
these sections.

This chapter covers the Visualizer and SQL sections of the notebook, and the
Language section used for bidirectional languages.


ΓòÉΓòÉΓòÉ 18.1. Changing your Visualizer profile options ΓòÉΓòÉΓòÉ

Use the first page of the Visualizer section to set the national language,
currency and number representation, and common operations.

Language       Select the language you want to work in. For example, to work in
               U.S. English, select ENU.
Currency       Specify the symbol to be used when displaying currency figures.
               You can also specify the position of the currency symbol.
Numbers        Specify:

   o The character to be used as the thousands separator
   o The character to be used as the decimal separator
   o The number of decimal places to be displayed
Common         Specify if you want:

   o To be prompted when you try to replace an existing Visualizer object with
     an object you are trying to save.

   o To view or change the print settings each time you print an object.

   o To display the names of shortcut keys to be on object menus.

   o To run a query when you open it.

Use the second page to set the date and time formats.

Date format    Enter the short and formal date formats to be used when
               displaying dates. In the Date separator field, type the
               character to be used to separate the month, day, and year.
Time format    Enter the time format to be used when displaying times. In the
               Time separator field, type the character to be used to separate
               the hours, minutes, and seconds.

Use the third page to set options for connecting to AS.

Profile             Specify the server profile to be used to establish the
                    connection to AS. The default is DASSERV.
Logon command       Specify the name of the command file that is used to log on
                    to the host computer. The default is blank. Two command
                    files are supplied with QUERY FOR OS/2: FTBLNVM.CMD (for
                    VM), and FTBLNMV (for MVS).
AS setup code       Specify the parameters to be used when AS is started. This
                    field should be used to specify a default application code.
Host codepage       Specify the translation table to be used when transferring
                    data between AS and Visualizer.


ΓòÉΓòÉΓòÉ 18.2. Changing your SQL profile options ΓòÉΓòÉΓòÉ

The SQL section contains the settings used when browsing or updating SQL tables
or SQL views.

Fetch limit                   Specify the maximum number of rows that can be
                              read from a database table or view. The default
                              is 2000. The limit can be from 1 through 64000
                              rows.
Data Viewer timeout           Specify how long the Data Viewer can remain
                              unused before the data is rolled back.

   Timeout radio button and spin button Specify how long the Data Viewer can
                  remain unused. Select Timeout and set the number of minutes
                  (from 1 through 99).

                  The default timeout is 10 minutes.
   No timeout     Select this if you do not want the data to be rolled back
                  after a specific period.
Read locking                  Specify how a database table or view is locked
                              when it is read from the Data Viewer.

   Uncommitted read Enables you to read a table, ignoring any database locks.
                  However, because this option does not lock the table, other
                  people can update the table when you are updating it. The
                  latest data is fetched, even when this data has not yet been
                  committed to the database.
   Cursor stability Enables you to open a table and lock the rows as they are
                  fetched. This prevents other people from updating the locked
                  rows while you are working with them. This is the default.
   Repeatable read Behaves like Cursor Stability, except that read locks are
                  not released as you scroll through the data. When you select
                  a set of data, all the data in that set is locked. The data
                  is unlocked when you select a new set of data, or when you
                  scroll through all the fetched data.
Update locking                Specify how a database table or view is locked
                              when you open it in update mode from the Data
                              Viewer.

   Exclusive      Gives you exclusive control over the table. No one else can
                  update the data when you are updating it. This is the
                  default.
   Shared         Enables other people to read and update the table when you
                  are updating it, provided that they are using different parts
                  of the table.


ΓòÉΓòÉΓòÉ 18.3. Changing your settings for bidirectional languages ΓòÉΓòÉΓòÉ

When you install Visualizer products with bidirectional versions of OS/2, the
Profile notebook contains a Language section. You can use this to change the
settings for your language. Not all bidirectional languages are the same; only
the applicable settings appear in the Language section.


ΓòÉΓòÉΓòÉ 18.3.1. Deciding how data is displayed and exchanged ΓòÉΓòÉΓòÉ

Use the first page of the Language section to set options for displaying and
exchanging data. Some bidirectional languages do not require character or
numeral shaping.

It is important that you use suitable settings when you are transferring data
between VM/AS or AS/400 databases and Visualizer. The following sections
describe the settings you should use.


ΓòÉΓòÉΓòÉ 18.3.1.1. Settings for VM/AS ΓòÉΓòÉΓòÉ

If you are transferring data to or from VM/AS, use these settings:

Text Type                Visual
Character Shape          Save Shaped


ΓòÉΓòÉΓòÉ 18.3.1.2. Settings for AS/400 databases ΓòÉΓòÉΓòÉ

If you are transferring data to or from AS/400 databases, use these settings:

Text Type                Visual
Text Orientation         Left to Right
Symmetric Swapping       Off
Character Shape          Save Shaped


ΓòÉΓòÉΓòÉ 18.3.1.3. Settings for local data ΓòÉΓòÉΓòÉ

If you are using local data on your own workstation (for example, data in
Visualizer tables), use these settings:

Text Type                Implicit (this gives better sorting and searching
                         results)
Character Shape          Automatic (this is best if Text Type is Implicit)
Numeral Shape            Context (this is best if Text Type is Implicit)


ΓòÉΓòÉΓòÉ 18.3.2. Changing keyboard operations ΓòÉΓòÉΓòÉ

Use the second page of the Language section to change the way Visualizer
accepts keyboard data, and to use specific hot-keys. Some bidirectional
languages do not require the character or numeral shaping hot-keys that are set
by the check boxes from Automatic to Save Shaped in


ΓòÉΓòÉΓòÉ 19. Using Query for OS/2 objects from the OS/2 command line ΓòÉΓòÉΓòÉ

You can use the OS/2 command line to open, run, print, and create QUERY FOR
OS/2 objects.


ΓòÉΓòÉΓòÉ 19.1. Opening Query for OS/2 objects from the command line ΓòÉΓòÉΓòÉ

To open a QUERY FOR OS/2 object from an OS/2 command line, enter:

ftbas3 /cclass_name /nobjectname

where:

class_name   An object class name from the following list:

   IBMTABLE             Visualizer Table object

   IBMSQLSTATEMENT      SQL Statement object

   IBMQUERY             Visualizer Query object

   IBMREPORT            Visualizer Report object

   IBMSQLTABLE          SQL Table object

   IBMSQLVIEW           SQL View object

objetcname   The name of the object to be run, including the path, if
             necessary.


ΓòÉΓòÉΓòÉ 19.1.1. Running Query for OS/2 objects from the command line ΓòÉΓòÉΓòÉ

You can run Visualizer queries and SQL Statements from the command line without
opening them. To do this, use the following command:

ftbas3 /cclass_name /nobjectname /k[run]

where:

class_name   An object class name:

   IBMQUERY             Visualizer Query object

   IBMSQLSTATEMENT      SQL Statement object

objetcname   The name of the object to be run, including the path, if
             necessary.
For example:

ftbas3 /cIBMQUERY /nmyquery /k[run]


ΓòÉΓòÉΓòÉ 19.1.2. Running SQL tables and SQL views from the command line ΓòÉΓòÉΓòÉ

You can open and browse SQL tables and SQL views by using the following
command:

ftbas3 /cclass_name !qcreatorid.tablename !ddatabase

where:

class_name   An object class name:

   IBMSQLTABLE          SQL Table object

   IBMSQLVIEW           SQL View object

creatorid    The creator of the table or view. This is optional-you need to
             specify this only when you are not the creator of the table or
             view.

tablename    The name of the table or view to be opened.

database     The name of the database the table or view is located in.

Note:

The ! parameters must come immediately after the / option.

QUERY FOR OS/2 automatically picks up the user ID and password specified in the
Database Settings window. Therefore, if a database requires a user ID and a
password, specify them by using the Database Settings window.


ΓòÉΓòÉΓòÉ 19.1.3. Using variable values from the command line ΓòÉΓòÉΓòÉ

Some QUERY FOR OS/2 objects use variables. These objects are Visualizer Query,
Visualizer Report, and SQL Statement. To use variable values with these objects
when running them from the command line, enter:

ftbas3 /cclass_name /nobjectname /@p1=p1value@p2=p2value

where:

class_name           An object class name:

   IBMQUERY             Visualizer Query object

   IBMSQLSTATEMENT      SQL Statement object

   IBMREPORT            Visualizer Report object

objectname           The name of the object to be run, including the path, if
                     necessary.

p1, p2, ...          A variable value defined in the object

p1value, p2value, ... The value the variable uses when you run the object.


ΓòÉΓòÉΓòÉ 19.2. Printing Query for OS/2 objects from the command line ΓòÉΓòÉΓòÉ

You can print QUERY FOR OS/2 objects to a file or to a printer.


ΓòÉΓòÉΓòÉ 19.2.1. Printing Query for OS/2 objects to a file ΓòÉΓòÉΓòÉ

Printing an object to a file is the same as exporting it. You can print
Visualizer tables and Visualizer reports to a file by using the following
command:

ftbas3 /c.class_name. /n.objectname. "/k[copyto(.filename, format, pwidth,
pdepth.)]"

where:

class_name   An object class name from the following list:

   IBMTABLE             Visualizer Table object

   IBMREPORT            Visualizer Report object

objectname   The name of the object to be printed, including the path, if
             necessary.

filename     The name of the output file.

format       A file format. The following file formats are available for
             Visualizer tables:

   o DIF
   o DBF
   o EDIF
   o Flat
   o IXF
   o List
   o PCIXF

             The following file formats are available for Visualizer reports:

   o IBMREPORTRESULTS
   o PRINTFILE (a flat ASCII file with page breaks)
   o TEXT (a flat ASCII file without page breaks)

pwidth       The page width, specified in characters. This value can range from
             1 to 254. If this option is not specified, a default value of 80
             is used.

             This option is only used with TEXT and PRINTFILE formats.

pdepth       The page depth, specified in characters. This value can range from
             1 to 63999. If this option is not specified, a default value of 70
             is used.

             This option is only used with TEXT and PRINTFILE formats.


ΓòÉΓòÉΓòÉ 19.2.2. Printing Query for OS/2 objects to a printer ΓòÉΓòÉΓòÉ

You can print the following QUERY FOR OS/2 objects to a printer:

o SQL tables
o SQL views
o Visualizer tables
o Visualizer reports

To print an object to a printer from the command line, use the following
command:

ftbas3 /cclass_name /nobjectname "/k[print(printername, dlgoption, method,
jobtitle)]"

where:

class_name   An object class name from the following list:

   IBMSQLTABLE          SQL Table object

   IBMSQLVIEW           SQL View object

   IBMTABLE             Visualizer Table object

   IBMREPORT            Visualizer Report object

objectname   The name of the object to be printed, including the path, if
             necessary.

printername  The name of the printer device.

dlgoption    Whether the Printer Settings dialogs are displayed. Set this to 1
             to display the dialogs, or 0 to suppress them.

method       The amount of the object to be printed.

   TABLE     Prints the whole table

   SUBSET    Prints the subset of the table (if a subset of the table has been
             defined and saved). If this option is selected and there is no
             subset of the table, the whole table is printed.

             This option is only used with the Visualizer Table object.

jobtitle     The name of the print job.

             This option is only used with the Visualizer Table object.


ΓòÉΓòÉΓòÉ 19.3. Creating and starting new Query for OS/2 objects ΓòÉΓòÉΓòÉ

To create a new QUERY FOR OS/2 object, copy the template for the object to a
new location and rename it by entering:

copy ftwpath\object_type objectname

where:

ftwpath      The fully qualified path of your FTW directory. This is the drive
             and directory (or directories) where QUERY FOR OS/2 is installed.

object_type  The type of object you are creating. This can be:

   sql       SQL Statement

   query     Visualizer Query

   report    Visualizer Report

objectname   The full path and name of the new object

Open the new object as described in Opening Query for OS/2 objects from the
command line.


ΓòÉΓòÉΓòÉ 19.4. Working with databases from the command line ΓòÉΓòÉΓòÉ

To associate an SQL Statement with a database, use !d as the final option of
the command:

ftbas3 /cibmsqlstatement /nSQLstatement /k[run] !ddatabase

where database is the name of the database with which you want to associate the
SQL Statement. You can use this option only if the SQL Statement has no
currently associated database.


ΓòÉΓòÉΓòÉ 19.5. Setting object classes ΓòÉΓòÉΓòÉ

Some methods used to exchange Visualizer objects with other users may result in
the loss of the object's class information. If this happens, the object icon
will not be displayed, and the object cannot be opened.

You can use the FTBTYPE.CMD REXX command file provided with Visualizer to
assign the correct object class to an object. You can also assign a class to
Personal AS objects.

To apply the correct object class, enter:

ftbtype file objectclass

where file is the file whose object class is to be restored, and objectclass is
the object class that you are applying to the file.

Note:   If you apply the incorrect class to an object, or try to apply a class
to a file that is not a Visualizer object, a message will be issued when you
attempt to open the object.

For example, to apply the object class for a chart (IBMQUERY) to a file called
MYQUERY located in the C:\USER directory, enter:


ftbtype c:\user\myquery IBMQUERY


ΓòÉΓòÉΓòÉ 20. Common OS/2 tasks ΓòÉΓòÉΓòÉ

This appendix describes how to create and rename an object.


ΓòÉΓòÉΓòÉ 20.1. Creating a new object ΓòÉΓòÉΓòÉ

You can create a new object from a template or from an existing object.


ΓòÉΓòÉΓòÉ 20.1.1. Creating a new object from a Query for OS/2 template ΓòÉΓòÉΓòÉ

You can create a new object from a template by using drag and drop. Dragging
and dropping a template creates a new occurrence of the object that the
template represents. The new object is created in the folder where the template
is dropped and has the same settings and contents as the template.

Use care when working with templates. If you change the settings for a
template, any object created from that template has the templates current
settings.

To create a new object:

 1. Open the Visualizer folder.

 2. Open the Shadows of Templates folder.

 3. Hold down mouse button two and drag a template from the Shadows of
    Templates folder to a target folder or to the desktop.

If you are creating an SQL table or SQL view, drop the template on a specific
SQL database. The SQL table or SQL view will be stored in this SQL database.


ΓòÉΓòÉΓòÉ 20.1.2. Creating a new object from an existing object ΓòÉΓòÉΓòÉ

When you create a new object from an existing object, the new object is created
with default settings.

To create a new object from an existing object:

 1. Display the pop-up menu for an object.
 2. Select Create another. A new object with default settings is created.


ΓòÉΓòÉΓòÉ 20.1.3. Renaming an object ΓòÉΓòÉΓòÉ

After you create a new object, you might want to change the name of the object.

To rename an object:

 1. Hold the Alt key, then select the title of the object you want to change.

 2. Type the new name.

 3. Use the Backspace key or Delete key to erase the previous name.

 4. Move the mouse away from the name and press mouse button one.


ΓòÉΓòÉΓòÉ 20.2. Creating a template with a specific definition ΓòÉΓòÉΓòÉ

You can customize a template and use it to create multiple objects with the
same settings. This is useful if you want, for example, all your reports to
contain the same heading with a specific font and color.

You can create templates with specific definitions for all Visualizer objects.

To create a template with a specific definition:

 1. Create a new object as described in Creating a new object from a Query for
    OS/2 template. If you are working with an SQL Table template or an SQL View
    template, do not drop it on an SQL database. Instead, drop it on your
    desktop.

    An icon for the new object is displayed.

 2. Customize the object to include the features you want to use in future
    objects.

 3. Rename the object as described in Including data in text.

 4. Open the notebook for the object to the General page.

 5. Select the Template checkbox. The icon on your desktop for the object
    changes to a sticky pad with the object icon displayed on it.

 6. Close the notebook.

To use the template, create a new object from the template. An icon for the new
object is displayed. Define the object as outlined in this book for that
object.


ΓòÉΓòÉΓòÉ 21. Using QMF forms with Query for OS/2 ΓòÉΓòÉΓòÉ

When you import a QMF form into a Visualizer report, most of the formatting
options are converted into the equivalent Visualizer report settings. This
appendix lists QMF options that are not converted automatically, but which you
can set manually in the report. It also lists QMF options that have no
equivalent in a Visualizer report, and are ignored when you import a QMF form
containing them.


ΓòÉΓòÉΓòÉ 21.1. QMF options with equivalent Visualizer report settings ΓòÉΓòÉΓòÉ

The following QMF formatting options have equivalent settings in a Visualizer
report, but they must be set up manually using the appropriate report settings
windows after the form has been imported:

o Calculated columns

o Fixed columns that do not scroll horizontally

o Detail blocks that are conditionally formatted

o The number of blank lines appearing before final text

o The option of restarting page numbers at the highest break level

o Descriptive words, such as SUM and COUNT, that are added to column headings
  when the data is grouped

o Columns ordered across the report based on GROUP and aggregation usage codes

o The choice of time separator character

See the appropriate section in Analyzing and presenting data using reports for
information on how to change these settings.


ΓòÉΓòÉΓòÉ 21.2. QMF options with no equivalent Visualizer report settings ΓòÉΓòÉΓòÉ

The following QMF formatting options either exceed the limits of the equivalent
Visualizer report settings, or have no equivalent in a Visualizer report:

o A break column that has a break level greater than 5

o Two or more columns with the same break level

o Asterisks used as default break text

o The FIRST and LAST usage codes

o The TCPCT (cumulative percentage of total) usage code

o The CSUM (cumulative sum) usage code

o Column values that have been substituted with the results of the PCT, CPCT
  and TPCT usage codes

o Column values formatted in hexadecimal, binary or scientific notation

o Column values formatted in decimal notation with leading zeros or a percent
  symbol

o Locally defined date and time formats

o The variables &ROW, &COUNT, &CALCn and &an

o Non-displayed summary columns created by aggregation usage codes in across
  reports

o Column widths greater than 254 characters

o Wrapping of column values onto a new line, including smart wrapping on
  specified characters

o Wrapping of text onto a new line when the report width is exceeded

o Detail heading text and detail block text displayed on each detail line

o Detail blocks that, when printed, each appear on a new page

o Final report text (such as END OF REPORT ) that appears on a new page

You can import a QMF form that uses these formatting options, but they will not
appear in the resulting Visualizer report.


ΓòÉΓòÉΓòÉ 22. Bibliography ΓòÉΓòÉΓòÉ

The following lists do not include all the books for a particular library. To
order copies of the books listed here, or to get more information about a
particular library, see your IBM representative.

IBM DATABASE 2 OS/2 (DB2/2) Version 1 Release 1

   IBM DATABASE 2 OS/2 SQL Reference, S62G-3667

IBM DATABASE 2 Client Application Enabler/2 (CAE/2) Version 1 Release 1

   IBM DATABASE 2 Client Application Enabler/2 User's Guide, SC09-1627

IBM Operating System/400* Version 2

   Application System/400* Systems Application Architecture* SQL/400 Reference,
   SC41-9608

IBM DATABASE 2 AIX* (DB2/6000) Version 1 Release 1

   DATABASE AIX/6000* and DATABASE OS/2 SQL Reference, SC09-1574

SQL/Data System (SQL/DS) for VM (Version 3 Release 3) and VSE (Version 3
Release 4)

   SQL/DS: SQL Reference for IBM VM Systems and VSE, SH09-8087

IBM DATABASE 2 Version 2 Release 3

   IBM DATABASE 2 SQL Reference, SC26-4380


ΓòÉΓòÉΓòÉ 23. Visualizer Education Request Form ΓòÉΓòÉΓòÉ

To receive details on Visualizer Education, please complete this form and mail
or fax it to:

IBM United Kingdom Limited
ContactPoint
Warwick Software Development Laboratory
PO Box 31, Birmingham Road
WARWICK
United Kingdom  CV34 5JL

Telephone:                    [phone]
Fax number:                   [phone]
IBM Mail Exchange:            GBIBMNRT at IBMMAIL
IBM Internal Network:         MARKET at ASICVM1
Bitnet:                       MARKET at VNET
Internet:                     [e-mail]

IBM Corporation
FAO Mary Crocket
1 East Kirkwood Blvd
Roanoke
Texas  762 99 0001
USA

Telephone:                    [phone]
Fax number:                   [phone]
IBM Mail Exchange:            USIB5Z9J at IBMMAIL
IBM Internal Network:         JONESSH at MSNVM1
Internet:                     [e-mail]

Query for OS/2                          Procedures for OS/2

[]  Introductory education              []  Introductory education
[]  Advanced education                  []  Advanced education

Charts for OS/2                         Development for OS/2

[]  Introductory education              []  Introductory education
[]  Advanced education                  []  Advanced education

Statistics OS/2                         Plans for OS/2

[]  End-user education                  []  End-user education

Other (Please detail your requirements)

ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ
ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ
ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ

Name:                Address:

Company:

Phone No.:


ΓòÉΓòÉΓòÉ 24. Visualizer Information Request Form ΓòÉΓòÉΓòÉ

To receive information about Visualizer products, just call:

In U.S.A., 1-800-IBM-CALL, extension 137

In Canada, 1-800-465-1234, extension 2002

Elsewhere, [phone]

Alternatively, complete this form and fax it to:

[phone]

[]  Query for OS/2                 []  Procedures for OS/2
[]  Charts for OS/2                []  Development for OS/2
[]  Statistics for OS/2            []  Plans for OS/2
[]  Ultimedia Query for OS/2

Other (Please detail your requirements)

ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ
ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ
ΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇΓöÇ

Name:                Address:

Company:

Phone No.: