 |
» |
|
|
|
This task explains how IMAGE/SQL users select TurboIMAGE/XL data
with SQL. Getting Ready To successfully select TurboIMAGE/XL data with IMAGE/SQL,
users need to know the following:
How to use their available SQL interface. The
examples in this manual use ISQL, which also requires familiarity with the
SQL SELECT statement. The names of the mapped tables and/or views to which
they have access. Which columns map to TurboIMAGE/XL search items, key items (with or without B-Tree indices), and items on which third-party indices exist.
Under certain
circumstances, using these mapped columns when selecting data
can improve performance (see Task Reference). Which data is of type FLOAT. When selecting this data, users should specify a
range of values
rather than a particular number. This is necessary because some precision is
lost when converting to FLOAT. Also, a decimal point must be included in the value for better performance.
Performing the Task In the example below, a CONNECT statement for PartsDBE is issued from the
ISQL prompt. A select statement then retrieves all the data from the view
SALES.VENDOR_V13.
isql=> CONNECT TO 'PartsDBE';
isql=> SELECT * FROM SALES.VENDOR_V13;
select * from sales.vendor_v13;
----------------+--------------------------+------------+-----+------
VENDOR |STREET |CITY |STATE|ZIP
----------------+--------------------------+------------+-----+------
Celtic Graphics |105 19th Ave. |Seattle |WA |98115
Trident 3D |55 Homestead Road |Cupertino |CA |95014
Ablrn Tech. |90 Marina Way |Berkeley |CA |94708
Space Ent. |110 Homestead Ave. |Cupertino |CA |95014
Cutler Micro |9442 E. 57th Ave. |Seattle |WA |98115
Seminational Co.|5000 Marina Way |San Diego |CA |92093
. . . . .
. . . . .
|
Users can also join data from more than one view.
The query in the following example retrieves data from two views, both
with a column containing product numbers.
The product number, the quantity, and the name of an alternative vendor
is selected when the product number
appears in both views. Specifically, the query selects the following columns: OTHER_VENDORS_1 from view SALES.INVENTORY_14 QUANTITY from view SALES.SALES_V14 SALES.SALES.PRODUCT# from view SALES.SALES_V14
Note that to eliminate ambiguity, because PRODUCT#
exists in both views, the fully qualified column name must always be specified.
isql=> SELECT OTHER_VENDORS_1, QUANTITY, SALES.SALESV_14.PRODUCT#
> FROM SALES.INVENTORY_V14, SALES.SALES_V14
> WHERE SALES.INVENTORY_V14.PRODUCT#=SALES.SALES_V14.PRODUCT#;
select other_vendors_1, quantity, sales.salesv_14.product# from sales.inv..
----------------+---------+--------
OTHER_VENDORS_1 |QUANTITY |PRODUCT#
----------------+---------+--------
Ablrn Tech. | 4 |P4943
Celtic Graphics | 2 |P6644
Celtic Graphics | 10050 |P3523
. . .
. . .
|
In this example, whenever the product numbers in the two views match, ISQL
displays columns PRODUCT#,
OTHER_VENDORS_1, and QUANTITY. Task Reference The structure of the TurboIMAGE/XL database cannot be changed with IMAGE/SQL
commands. Therefore, SQL statements that alter the structure of the
database are not available to IMAGE/SQL users. When users have access to the entire data entry, they can select data from
the table itself. If they do not have access to the entire data entry, they
must select data from a view of the table created for them by IMAGE/SQL.
Table names are of the form OwnerName.MappedTableName.
View names are of the form OwnerName.MappedTableName_VUserClass#.
In WHERE clause, specifying columns that map to
TurboIMAGE/XL search items, key items (with or without B-Tree indices), or items that have third-party indices will improve performance under the
following conditions:
The WHERE clause compares a mapped column and a value
for equality:
isql=> SELECT * FROM SALES.SALES_V11
> WHERE PRODUCT# = '235'
> ...
|
When a column maps to an item which has a B-Tree or third-party index, an operator, other than equality, can also be used (such as > or >=).
The WHERE clause has more than one expression, each containing
a different mapped column. These subexpressions
are connected with the AND operator.
isql=> SELECT * FROM SALES.SALES_V11
> WHERE (product# = '234')
> AND (purchased_date = '032189')
> ...
|
The WHERE clause has more than one expression, each containing the same mapped
column. These subexpressions either use the IN operator or are connected with
the OR operator.
isql=> SELECT * FROM SALES.SALES_V11
> WHERE (product# IN ('224', '321'))
> ...
|
|
