33
Filtering Results
131
To make a column visible:
1. On the Results menu, click Reorder Columns.
2. In the Reorder Columns dialog box, select a hidden column in the Columns list.
3. Click Show ; then, click OK.
Narrowing the Range of Results
To help you analyze your data, you can focus on a particular range of results.
Specifying a range for results includes only the rows within that range. For
example, you can specify retailer credit limits between $200,000 and $600,000,
sales received between June 30 and December 31, and salespeople’s names between
the letters A and S. You can also focus on results outside a range.
To narrow a range of existing results:
1. In a Results window, point to Filter on the Results menu; then, click Range. The
Range Filter dialog box opens.
2. From the Where list, select the column you want to filter.
3. In the Is > Than Or = box, type the minimum value for the range.
4. In the Is < Than Or = box, type the maximum value for the range.
5. Do one of the following:
• To display the results that lie within the specified range, select Include Rows.
• To display the results that lie outside the specified range, select Exclude Rows.
6. Click OK.
You can also specify a range for results before running a query using the
BETWEEN and NOT BETWEEN operators in the qualification tree. For more
information, see “Finding Values in a Range” on page54.
Performing Calculations on Results
You can perform some basic calculations on results. You can also create an
attribute that performs more complex calculations you define as part of a query.
To display basic calculations for a results set:
ɼ
On the Results menu, point to Filter; then, click Compute.
In the Compute Results information box, minimum, maximum, sum, and
averages for each column appear.
34
Chapter 8: Fine-Tuning Results
132
Combining Results Sets
In order to enlarge the scope of your data, you can combine results from multiple
queries into one results set. You can do the following operations:
• append the rows of one results set to another
• join one or more columns of one results set to another
For information on troubleshooting combined results, see the Help for your BI
Query application.
The results sets can be from queries run in different Design windows within the
model. Because different windows can connect to different databases, you can
combine information from two different databases.
You can combine the results in two ways:
• Before you run the corresponding queries. In this case, you use the Super Query
window to combine the results of the two queries.
• After you run the queries. In this case, you specify the results sets you want to
combine directly.
If you save the query after combining results, you can open and edit it in the Super
Query window. (However, you cannot save the corresponding SQL string as a text
file.) You can also link the combined query to a button to perform the combine
operations automatically.
For more information on buttons, see the BI Query Data Models User’s Guide.
Rules for Appending Rows
When you have similar sets of results (for instance, identical kinds of data from
two different years) that you want to present together, you can append the rows of
one results set to another. If the two sets have different column names, BI Query
copies the name(s) from the first set to the combined set.
Adhere to the following rules when you append the rows of one results sets to the
rows of another:
• The data types of the columns in the first results set must match the data types
of the corresponding columns in the second results set. For example, if the first
results set contains two columns, and their data types are character and
numeric, respectively, the first two columns of the second results set must also
be character and numeric.
63
Combining Results Sets
133
• The order of columns in one results set must match the order of columns in the
other. One set may have additional columns that the other does not have as
long as these extra columns are at the end (on the right).
Example
In the following example, the rows from Query Results 2 have been appended to
Query Results 1 to form a new results set.
Query Results 1
Query Results 2
Name
Sales ($)
Name
Sales ($)
State
Abicon Inc.
12,000
Bainstream Inc.
10,000
New York
Textal Ltd.
113,000
Knatl Corp.
2,000
California
Unisystems
10,145
MonoLitho
2,100
Ohio
Ziptech
6,430
Textronics
60,000
Illinois
Appended Results
Name
Sales ($)
State
Abicon Inc.
12,000
NULL
Textal Ltd.
113,000
NULL
Unisystems
10,145
NULL
Ziptech
6,430
NULL
Bainstream Inc.
10,000
New York
Knatl Corp.
2,000
California
MonoLitho
2,100
Ohio
Textronics
60,000
Illinois
32
Chapter 8: Fine-Tuning Results
134
Combining Results by Appending Rows
Use the Append Rows dialog box to append one results set to another.
To append rows using existing results sets:
1. Open the results sets you want to combine, either by running the
corresponding queries or by opening saved results set files.
2. On the Results menu, point to Combine; then, click Append Rows. The Append
Rows dialog box opens.
3. From the First Results list, select the first results set. By default, this is the active
results set.
4. From the Second Results list, select the results set you want to append to the first
set. By default, this is the first results set (other than the active results set) to be
opened in the current session.
5. Click OK.
To append rows using queries:
1. Specify the first query by doing one of the following:
• Open an existing super query. With the query active, point to Super Queries
on the Query menu, and then click Show Super Query.
• Run a query. With the results set active, point to Super Queries on the Query
menu, and then click Show Super Query.
The Super Query window opens.
2. On the Query menu, point to Super Queries; then, click Append Rows. The Append
Rows dialog box opens.
3. Do one of the following:
• To append the current query, select Current Query.
• To append a saved query, select Saved Query. In the Choose Query dialog box,
select the query and click Open.
• To append an open freehand or super query, click Opened Query. In the
Choose Open Query dialog box, select the query and click OK.
4. In the Append Rows dialog box, click OK. In the Super Query window, an append
operation appears in the tree list above the component queries.
35
Combining Results Sets
135
Rules for Joining Columns
You can join columns from different results sets to produce a new results set. The
new results set can contain data from more than one data object (including actual
relationships and query data objects) from more than one Design window.
Typically, the BI Query administrator specifies the join operations between data
objects when the model is created. However, you can produce the same results by
joining columns from different results sets. In effect, you are creating a
relationship between results sets that is equivalent to a relationship between joined
data objects.
For more information on creating joins between data objects, see the BI Query
Data Models User’s Guide.
By convention, a column join has a “left” results set and a “right” results set. You
specify which column in the left set joins to which column in the right. (You can
join more than one pair of columns.) The combined results set represents each
pair of joined columns in a single column.
Adhere to the following rules and guidelines when joining a column of one results
sets with a column of another:
• The joined columns must have the same data type. However, you can join
columns containing floating point and money data types.
• If the joins you need already exist in the data model, you may want to create a
query that retrieves the results rather than a super query that combines results
sets. The DBMS—which can handle a single query at a time—may be able to
optimize the join operations more efficiently than BI Query.
Reasons for Joining Columns
You may want to join columns of results sets for the following reasons:
• The Design window does not provide joins for the attributes you need to join.
• The data model does not include permission to create relationships between
tables.
• The database does not support outer joins.
• You need to combine results from more than one database.
• After examining recent results sets, you want to join certain columns but do
not want to rerun the component queries (for example, they may require too
much processing time).
24
Chapter 8: Fine-Tuning Results
136
Types of Joined Columns
When you join columns from two results sets, you can use one of four types of
join: an inner join, a full outer join, a left outer join, or a right outer join.
For more information on types of joins, see the BI Query Data Models User’s
Guide.
Inner Join
An inner join includes only those rows that have an exact match in the joined
columns. (BI Query actually performs a type of inner join called a natural join,
which displays the joined columns only once in the combined results set.)
Full Outer Join
A full outer join includes all rows from both results sets, even if they do not match
in the joined columns.
Left Outer Join
A left outer join includes all rows from the left results set and those from the right
that match in the joined columns.
Right Outer Join
A right outer join includes all rows from the right results set and those from the
left that match in the joined columns.
Inner join
Full outer join
Left outer join
Right outer join
60
Combining Results Sets
137
Unmatched data in a full, left, or right outer join is represented by a
NULL
in the
joined column.
Example
In the following example, the first set of query results shows the total sales for
products that a company has produced in the U.S. The second set of results shows
the total payments received to date for all products sold by the company, including
those produced outside the U.S.
An inner join of both results sets on Product ID shows all American products by
the company that have been sold and received some payment to date. A left outer
join on the same column shows the sales and payments of all American products,
regardless of payment.
Query Results 1
Query Results 2
Product ID
Sales ($)
Product ID
Amount Received ($)
101
15,000
101
13,050
102
52,303
102
52,303
213
995
331
1,061
331
2,561
382
10,090
511
8,000
449
900
514
2,303
818
612
818
612
Inner Join on Product ID
Product ID
Sales ($)
Amount Received ($)
101
15,000
13,050
102
52,303
52,303
331
2,561
1,061
46
Chapter 8: Fine-Tuning Results
138
Combining Results by Joining Columns
Use the Join Columns dialog box to specify which results sets to join on which
columns. You must also specify the type of column join. The joined columns must
have the same (or similar) data type. You can join one or more pairs of columns.
(Typically, you should join columns that represent primary keys.)
You can join columns either by specifying the results sets directly, or by specifying
the queries that generate the results sets.
To join columns using existing results sets:
1. Open the two results sets you want to join.
2. On the Results menu, point to Combine, and then click Join Columns.
3. Select the “left” results set from the Left Results list. By default, this is the active
results set.
818
612
612
Query Results 1 Left Outer Joined to Query Results 2 on Product ID
Product ID
Sales ($)
Amount Received ($)
101
15,000
13,050
102
52,303
52,303
213
995
NULL
331
2,561
1,061
511
8,000
NULL
514
2,303
NULL
818
612
612
Inner Join on Product ID
Product ID
Sales ($)
Amount Received ($)
34
Combining Results Sets
139
4. Select the “right” results set from the Right Results list. By default, this is the
first results set (other than the active results set) to be opened during the
current session.
5. Do one of the following:
• To create an inner join, clear the Include All Left Rows and Include All Right
Rows check boxes. (This is the default setting.)
• To create a left outer join, select Include All Left Rows.
• To create a right outer join, select Include All Right Rows.
• To create a full outer join, select both check boxes.
6. Select the columns you want to join (up to eight pairs) in the order in which
you want to join them.
A joined column appears only once in the combined results set.
7. Click OK.
To join columns using queries:
1. Open or run one of the queries whose results you want to join. (This query will
produce the “left” results set.)
2. With the query or results set active, point to Super Queries on the Query menu,
and then click Show Super Query. The Super Query window opens.
3. On the Query menu, point to Super Queries, and then click Join Columns. The Join
Columns dialog box opens.
4. Specify the query that will produce the “right”results set (or the “bottom
branch” with respect to the super query) by choosing one of the options from
the Query for the bottom branch list:
• If you want to use the current standard query, click Current Query.
• If you want to use a saved query, click Saved Query. In the Choose Query dialog
box, select the query and click Open.
• If you want to use an open freehand or super query, click Opened Query. In
the Choose Open Query dialog box, select the query and click OK.
5. Do one of the following:
• To create an inner join, clear the Include All Left Rows and Include All Right
Rows check boxes. (This is the default setting.)
• To create a left outer join, select Include All Left Rows.
51
Chapter 8: Fine-Tuning Results
140
• To create a right outer join, select Include All Right Rows.
• To create a full outer join, select both check boxes.
6. Select the columns you want to join (up to eight pairs) in the order in which
you want to join them.
A joined column appears only once in the combined results set.
7. Click OK.
Troubleshooting Combined Results
If a query contains an error, you need to correct it before you can save or run the
query. Some common errors and their solutions are presented below.
Problem
Reason
Solution
Append: Column n
does not match
The attributes in the query
results you are appending do
not match because of one of
the following:
• you selected the attributes in
the first query in a different
order than the attributes in
the second
• an attribute used in one
query is missing from
another query
Edit the queries to
reselect the attributes
in a consistent order;
or edit the queries to
add the missing
attribute. (You cannot
edit an append
operation.)
Reorder Error: Missing
Column
Range Error: Missing
Column
Sort Error: Missing
Column
One of the attributes used in
the operation is missing
because of one of the
following:
• the attribute is missing from
the component query
• you hid the column with a
reorder operation
Edit the query to add
the missing attribute.
Documents you may be interested
Documents you may be interested
