39
Outer Joins in Queries
215
The results of the second join contain all rows from B, plus those from the results
of the first join that match B on the joined column (Country). However, the NULL
values supplied by A do not match any values in B in the joined column, so that
the corresponding rows from the first join are not part of the second set of join
results. In other words, the final result does not preserve all rows from table A. If
the join between B and C has order 1, a similar problem occurs (not all rows from
B are preserved).
Outer Join Syntax in Queries
BI Query supports the ODBC, Oracle, ANSI SQL/92, and “Classic” join syntax
types. “Classic” (ANSI SQL/89) syntax uses the legacy operators
*=
and
=*
. No
two syntax types can appear in the same Design window.
For more information on outer join syntax types, see the BI Query Data Models
User’s Guide.
The particular outer join syntax in use in a Design window (or across the data
model) depends on the connection assigned to that window (or model). If you
want to use a particular type of outer join syntax in a query, you do not have to
edit the existing joins; instead, you can change the syntax for the connection. In
this case, BI Query applies the specified syntax to all outer joins that use the
connection.
Because outer join syntax information is specific to a given connection, it is
stored in a connection file. When you change the syntax for a connection, it
applies to all Design windows and data models that use the corresponding
connection file. If you want your changes to be local to a particular window or
data model, create a new connection file, assign it to the window or model, and
then make the necessary changes to it. For more information on creating
connection files, see the BI Query Data Models User’s Guide.
Changing Outer Join Syntax for Connections
Use the Join Syntax dialog box to select an outer join syntax for a connection file.
The syntax you select must be supported by the connection.
To change the outer join syntax for a connection:
1. On the Host menu, click Connections.
2. In the Connections dialog box, select the connection file you want to use; then,
click Edit.
39
Chapter 12: Advanced Querying Techniques
216
3. In the Edit Connections dialog box, click Join Syntax. The Join Syntax dialog box
opens.
4. From the Join Syntax list, select the outer join syntax type that you want to apply
to the connection. The dialog box displays the default settings for the selected
syntax.
5. Click OK. The Join Syntax dialog box closes.
6. In the Edit Connection dialog box, click Save or Save As to save your changes.
Once you save the connection file with your changes, BI Query uses the
selected syntax for all Design windows and data models that use that
connection file.
Modifying the SQL for Queries
You can modify the SQL string generated by BI Query when you formulate a
query. However, you cannot directly modify the SQL for those queries (such as
super queries) in which the results have been modified or combined with other
results.
You must have the Edit SQL String permission to modify the SQL. You also need the
Show SQL String preference to display the SQL in the Query window. You can then
modify anything after the
SELECT
keyword (subject to any restrictions placed on
you by the DBMS). If you modify the SQL string incorrectly, the DBMS may
return an error message.
Changes you make to the SQL string should be the last changes you make to a
query; BI Query replaces any changes you make in the Query window with any
changes you make using attribute or Design windows.
BI Query expects results using the data format of the query selected in the Design
window. The modified query must return compatible results from the database. If
it does not, the results may have missing, misaligned, or otherwise incorrect
columns. To avoid this problem, do not change anything before the
WHERE
clause:
do not change the attributes you have selected; do not delete, add, or change the
function you have applied to them.
Modifying SQL in the Query Window
The SQL string in the Query window uses attribute names that appear in the
database. When you edit the string, you must use these database names, not the
display names that may appear in the attribute window.
36
Modifying the SQL for Queries
217
To edit a standard query by modifying its SQL string:
1. Make sure that the Edit SQL String permission is assigned and that the Show SQL
String preference is set.
2. Create, open, or retrieve the query.
3. Do one of the following:
• On the Query menu, click Show .
• On the Query toolbar or the Standard toolbar, click the Open Query button .
4. In the Query window (in SQL View mode), make the changes to the SQL string.
If you select Show Prompt Values, BI Query displays in the SQL string the
information provided for prompt qualifications the last time the query was
run. You cannot edit the SQL string while Show Prompt Values is selected.
If you subsequently modify the query in an attribute or Design window after
changing the query’s SQL, BI Query displays an alert box to warn you that the
modified SQL will be overwritten.
If the alert box appears, do one of the following:
• Click Change to overwrite the SQL string you modified.
• Click Keep to undo your changes in the attribute or Design window.
Including SQL Prefixes and Suffixes
You can fine-tune the SQL you send to the DBMS by specifying a prefix and/or a
suffix for every query and subquery that you run.
For example, if you're using a DB2 DBMS on a VAX machine, you can improve
database performance by appending the suffix
with ur
to every query to allow
“uncommitted reads.” If you're using Sybase, you can use the suffix
for browse
.
You can also specify whether the suffix occurs before or after the semicolon that
terminates the SQL for queries. This feature requires the Edit SQL String permission.
Use the Custom Check permission to improve the efficiency of the query. For
more information, see the Help for your BI Query application.
To specify a prefix and suffix:
1. In the Preferences dialog box, click Query Options.
2. In the Query Options dialog box, specify a prefix and/or a suffix.
21
Chapter 12: Advanced Querying Techniques
218
3. To place the suffix after the semicolon that terminates the SQL, clear Place
Suffix Before Semi-Colon.
4. Click OK.
Building Circular Queries
When the data model you are using contains two or more data objects whose
relationships form a circular path, you can build queries that use all the data
objects and all the relationships in that path. For example, if your data model
contains the Employees and Departments data objects, and they are connected by
the Work In and Managed By relationships, you can build queries that use both
data objects and both relationships.
Because a data object in a circular path may be used in two roles, BI Query
prompts you to specify whether or not to create a correlation name (or “alias”) for
the object. If you choose to do so, BI Query creates a temporary copy of the object
using the correlation name.
You can also choose not to create a correlation. The choice you make determines
how BI Query applies join conditions in your query and the extent to which your
results are restricted. A query that uses a correlation name lets you ask two
questions of the same data object and is less restrictive than a query that does not
use a correlation.
24
Building Circular Queries
219
Circular Queries with a Correlation
The last relationship you select when building a circular query determines which
objects can be aliased: you can create a correlation for one of the two objects that
form that last-selected relationship. The last-selected relationship also determines
which join conditions apply to the correlation.
BI Query creates the correlation name for an object using the following format:
object__relationship
where
• object is the first five characters of the name of the aliased object
•
__
is the literal string as written (two consecutive underscore characters)
• relationship is the first five letters of the last-selected relationship
For example, if the last relationship you select is called Managed By and you create
a correlation for an object named Employees, BI Query creates a second,
temporary copy of Employees called Emplo__Manag.
If you are using BI Query Admin, you can also use a correlation name to create a
permanent copy of a data object. Users of the data model can then create a query
involving the aliased data object more than once without creating a circular query
at the same time.
For more information on correlation names, see the BI Query Data Models
User’s Guide.
30
Chapter 12: Advanced Querying Techniques
220
Example
If you select the Managed By relationship last and create a correlation for
Employees, BI Query creates a second, temporary copy of Employees called
Emplo__Manag. The result is two copies of Employees: one connected to
Departments through the Work In relationship, the other, temporary copy
connected to Departments through Managed By.
When you double-click the original data object in the Design window, you are
prompted to specify which attribute window you want to use—the one for the
original data object or the one for its correlation. You can use one attribute
window to ask one question and the other attribute window to ask the second
question.
For example, suppose you want to use the Employees and Departments data
objects to identify employees that work for managers who make over $60,000. A
correlation is needed in this case since you require two types of information from
the Employees data object—the name of all employees and the name of managers
who earn over $60,000. Use the Employees data object to first identify the names
of the employees, and then use its correlation to identify managers who make over
$60,000. (You use the correlation for the second question because it is the one that
uses the join condition in the Managed By relationship.)
When you run the query, the DBMS processes it in two stages. Behind the scenes,
the DBMS first obtains a preliminary results set (names of all employees and the
departments they work in) by joining each row in Employees with each row in
Departments, and then applying the join conditions for Work In.
This is what you see in the
Design window
Behind the scenes, BI Query creates a temporary
copy of Employees and connects it to
Departments using the Managed By relationship
Documents you may be interested
Documents you may be interested
