HP 3000 Manuals HP 3000 Manuals
Task 15:Selecting TurboIMAGE/XL Data with SQL
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.[REV BEG]
* 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.[REV END]
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#.
[REV BEG]
* 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:[REV END]
* The WHERE clause compares a mapped column and a value for
equality:
___________________________________________
| |
| isql=> SELECT * FROM SALES.SALES_V11|
| > WHERE PRODUCT# = '235' |
| > ... |
___________________________________________
[REV BEG]
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 >=).[REV END]
* 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'))|
| > ... |
________________________________________________