HP 3000 Manuals HP 3000 Manuals
Selecting TurboIMAGE/XL Data with ALLBASE/SQL:
Task 14
This task explains how Turbo CONNECT users select TurboIMAGE/XL data with
ALLBASE/SQL.
Getting Ready
To successfully select TurboIMAGE/XL data with ALLBASE/SQL, Turbo CONNECT
users need to know the following:
* How to use the ALLBASE/SQL interface provided for them. The examples
here use ISQL, which also requires familiarity with the ALLBASE/SQL
SELECT statement.
* The names of the mapped tables and/or views to which they have access
(see Task Reference).
* Which columns map to TurboIMAGE/XL search items. 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.
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 alternate 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.inventor ... |
| ----------------+---------+-------- |
| 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
* Only read access to TurboIMAGE/XL data is provided. Therefore,
ALLBASE/SQL commands that modify data content are not available to
TURBO Connect users.
* The structure of the TurboIMAGE/XL database cannot be changed with
ALLBASE/SQL commands. Therefore, ALLBASE/SQL commands that alter the
structure of the database are not available to TURBO Connect 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 Turbo CONNECT. Table names are of the
form OwnerName.MappedTableName. View names are of the form
OwnerName.MappedTableName_VUserClass#.
* In WHERE clauses, specifying columns that map to TurboIMAGE/XL search
items will improve performance under the following conditions:
* The source search item is not of type Z or P.
* The WHERE clause compares a mapped column and a value for
equality:
___________________________________________
| |
| isql=> SELECT * FROM SALES.SALES_V11|
| > WHERE PRODUCT# = '235' |
| > ... |
___________________________________________
* 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'))|
| > ... |
________________________________________________
* For more detailed information about using the SELECT command, refer
to Appendix D, "Using the SELECT Command."