HP 3000 Manuals HP 3000 Manuals
Manipulating View Tables With the SQL Command
You can use the SQL command in the Host Batch Facility to query a table
and perform complex manipulations on it. The examples below show some of
the ways you can use SQL command files with view tables.
These examples come from a sample command file, SAMPCMD.PPCUTIL.HPOFFICE,
that is provided with the Access Server software. SAMPCMD uses two
sample databases, SAMPL1.PPCDICT.HPOFFICE and SAMPL2.PPCDICT.HPOFFICE,
that are also included with Access Server.
The SQL command has six clauses: SELECT, FROM, WHERE, ORDER BY, GROUP
BY, and HAVING. In the Administrator Utility, view tables can have four
clauses: ITEM, USING, WHERE, and SORT. The following table shows the
mapping between the two:
SQL Clause = Administrator Utility Clause
---------- ----------------------------
SELECT ITEM
FROM USING
WHERE WHERE
ORDER BY SORT
GROUP BY n/a
HAVING n/a
Unless otherwise noted, the SQL command's SELECT, FROM, WHERE, and
ORDER BY clauses shown in the examples can be used as Item, Using, Where,
and Sort clauses, respectively, in Administrator Utility view tables.
All commands in the examples are specified in upper and lower case. The
upper case part of the command is required to make the command unique.
The part in lower case is optional.
The following table shows the configured tables and items used in the
examples.
----------------------------------------------------------------------------------------------
| | | |
| Database | Table (Dataset) | Items |
| | | |
----------------------------------------------------------------------------------------------
| | | |
| SAMPL1 | | |
| | | |
| MGR.PPCDICT.HPOFFICE | CUSTOMER-MASTER | ACCOUNT, CUSTOMER, ADDRESS, |
| | | CITY, STATE |
| | | |
| | SALES-STAFF | SALES-REP, EMP-NBR, OFFICE, |
| | | PHONE, SALES |
| | | |
| | ORDER-DETAIL | ACCOUNT, EMP-NBR, |
| | | PRODUCT-NBR, SHIP-DATE, |
| | | ORDER-DATE, ORDER-DEPOSIT, |
| | | ORDER-NOTES, ORDER-QTY |
| | | |
| SAMPL2 | | |
| | | |
| MGR.PPCDICT.HPOFFICE | PRODUCT-MASTER | PRODUCT-NBR, PROD- DISCRIPT, |
| | | VENDOR-NAME, PRODUCT-TYPE, |
| | | UNIT-COST, PRICE, NOTES |
| | | |
| | INVENTORY | PRODUCT-NBR, WAREHOUSE, |
| | | BACKORDERFLG, ON-HAND-QTY |
| | | |
----------------------------------------------------------------------------------------------
For a description of SQL command parameters and syntax, see Chapter 13,
"Batch Processing with Command Files."
Selecting All Items
Here is an example of a simple query that finds all customers living in
California. The "*" in the SELECT clause indicates that all items are
chosen.
SQL "SELECT * FROM CUSTOMER-MASTER WHERE STATE = 'CA';"
If you were using this example in an Administrator Utility view table,
the view table would look like this:
Item Clause: *
Using Clause: CUSTOMER-MASTER
Where Clause: STATE = 'CA'
Query on Result
Here is an example of another simple query that finds all customers that
live in San Diego, California. The "*" in the FROM clause indicates that
the operation is being performed on the previously loaded table. (The
"*" in the FROM clause does not work in the Administrator Utility.)
SQL "SELECT * FROM * WHERE CITY = 'San Diego';"
Configuring IMAGE Array Items
Access PC does not support IMAGE array items and Access Server does not
allow any item manipulation on these items except ROW functions. (See
"ROW Functions" earlier in this appendix.) Therefore, the array's
individual elements must be broken out. In the following example, the
item SALES is an array of 12 packed decimal items that represent each
month of the year. The item is broken out into the 12 months, using the
SUB function to add a decimal place.
SQL SELECT SALES-REP,
JAN = SUB(SALES[1],1,P8,2),
FEB = SUB(SALES[2],1,P8,2),
MAR = SUB(SALES[3],1,P8,2),
APR = SUB(SALES[4],1,P8,2),
MAY = SUB(SALES[5],1,P8,2),
JUN = SUB(SALES[6],1,P8,2),
JUL = SUB(SALES[7],1,P8,2),
AUG = SUB(SALES[8],1,P8,2),
SEP = SUB(SALES[9],1,P8,2),
OCT = SUB(SALES[10],1,P8,2),
NOV = SUB(SALES[11],1,P8,2),
DEC = SUB(SALES[12],1,P8,2)
FROM SALES-STAFF;
CASE Statement
The CASE statement is a powerful and useful feature of view table
manipulation and can be used in many ways.
Example 1.
In this example, inventory quantities are checked to see if the quantity
on hand is getting low.
SQL "SELECT PRODUCT-NBR, ON-HAND QTY, &
INVENTORY-STATUS = CASE &
ON-HAND-QTY > 5000 : 'Good', &
ON-HAND-QTY > 1000 : 'Getting Low', &
ELSE 'Place Order' END &
FROM INVENTORY;"
Example 2.
In this example, the IMAGE data item BACKORDERFLG is expanded into a
longer, more intelligible string.
SQL "SELECT PRODUCT-NBR, ON-HAND QTY, &
BACK-ORDER-FLAG = CASE &
BACKORDERFLG = 'OK' : 'Not Backordered', &
BACKORDERFLG = 'BO' : 'Backordered' END &
FROM INVENTORY;"
SUB Function
The SUB function is a powerful feature that lets you define a new item by
extracting a substring from an item of any data type and giving it a new
item name. The new item can be redefined to any other data type that
Access Server supports, including the specification of a conversion type
and decimal point.
Example 1.
This example demonstrates the use of the concatenation operation combined
with the SUB function.
The phone number in this example is in the form (415) 555-1212. It will
be changed to the form 415-555-1212.
SQL "SELECT SALES-REP, EMP-NBR, OFFICE, &
PHONE = SUB(PHONE,2,C3) + '-' + SUB(PHONE,7,C8) &
FROM SALES-STAFF;"
Example 2.
This example demonstrates how an IMAGE item containing alphanumeric
characters can be converted to either integer or real.
The product number is in the form CCCMMM, where CCC is an alphabetic
string such as 'FIL' and NNN is a number such as '002'. Here the NNN
part will be SUBed out as an N type and converted to an integer and to a
real. The divide by 100 forces an implied decimal place of two places.
This operation on the item PRODUCT- NBR does not make any logical sense
but it demonstrates the feature.
SQL "SELECT INT-N-TYPE = SUB(PRODUCT-NBR,4,N3,,I), &
INT-R-TYPE = SUB(PRODUCT-NBR,4,N3,,R) / 100 &
FROM PRODUCT-MASTER;"
JOINs
JOINs can be performed in two ways within Access Server: By using the
JOIN keyword in the FROM clause or by specifying the JOIN criteria in the
WHERE clause. The two SQL statements below perform exactly the same
operation in two different ways. The CUSTOMER- MASTER dataset is being
JOINed with the ORDER-DETAIL dataset to show the custome name along with
other order information.
Example 1.
Here the JOIN is performed by using the JOIN keyword in the FROM clause.
SQL "SELECT CUSTOMER, PRODUCT-NBR, ORDER-QTY &
FROM CUSTOMER-MASTER<ACCOUNT> JOIN ORDER-DETAIL<ACCOUNT> &
ORDER BY CUSTOMER;"
Example 2.
Here the JOIN is performed by using the WHERE clause.
SQL "SELECT CUSTOMER, PRODUCT-NBR, ORDER-QTY &
FROM CUSTOMER-MASTER, ORDER-DETAIL &
WHERE CUSTOMER-MASTER.ACCOUNT = ORDER-DETAIL.ACCOUNT &
ORDER BY CUSTOMER;"
NOTE Because of limitations in the Access Server optimizer, you should
use the JOIN operator in the USING clause instead of the WHERE
clause to specify JOINs. The Access Server optimizer will be able
to set up the JOIN in such a way that in many cases the performance
of the JOIN will be better when using the JOIN operator in the
USING clause.
Left Outer JOINs
When a left outer JOIN (also known as an open JOIN) is performed, all
records from the primary table are included, even if a match is not found
in the secondary table. Items in the secondary table are filled with
values defined by the DEFAULT IS expression in the SELECT clause.
In the following example, the CUSTOMER-MASTER dataset is being
left-outer-JOINed with the ORDER-DETAIL dataset to show the orders
outstanding and to show explicitly which customers do not have any orders
outstanding. If a customer does not have any orders outstanding, then
items ORDER-DETAIL (PRODUCT-NBR and ORDER-QTY) will be set to a DEFAULT
value of '******' and -1, respectively. Note that the left outer JOIN
must be specified in the FROM clause and cannot be specified in the WHERE
clause.
SQL "SELECT CUSTOMER, PRODUCT-NBR, ORDER-QTY, &
DEFAULT PRODUCT-NBR IS '******', DEFAULT ORDER-QTY IS - 1 &
FROM CUSTOMER-MASTER<ACCOUNT>LEFTJOIN ORDER-DETAIL<ACCOUNT>&
ORDER BY CUSTOMER;"
To list only the customer that did not have any orders outstanding,
simply perform a query on the result, searching for the DEFAULT value.
Either PRODUCT-NBR or ORDER-QTY would work.
SQL "SELECT CUSTOMER &
FROM * WHERE ORDER-QTY = -1 &
ORDER BY CUSTOMER;"
Three-Way JOINs
You can JOIN up to 16 tables on both MPE V and MPE XL. Also, up to four
JOIN items can be specified for each JOIN. The following example
illustrates a simple JOIN of three tables. The first example specifies
the JOIN in the FROM clause; the second example specifies the JOIN in the
WHERE clause.
SQL "SELECT CUSTOMER, SALES-REP, PRODUCT-NBR, ORDER-QTY &
FROM (CUSTOMER-MASTER<ACCOUNT>JOIN ORDER-DETAIL<ACCOUNT>) &
<EMP-NBR> JOIN SALES-STAFF<EMP-NBR>;"
Here the JOIN is specified in the WHERE clause. Note the different
approach.
SQL "SELECT CUSTEMER, SALES-REP, PRODUCT-NBR, ORDER-QTY &
FROM CUSTOMER-MASTER, ORDER-DETAIL, SALES-STAFF &
WHERE CUSTOMER-MASTER.ACCOUNT = ORDER-DETAIL.ACCOUNT &
AND ORDER-DETAIL.EMP-NBR = SALES-STAFF.EMP-NBR &
ORDER BY CUSTOMER;"
Summarize Operations
Summarize operations are made easier with the SQL command. An important
thing to note here is that summarize operations (SUM, AVG, MIN, MAX, and
COUNT) are not allowed in Administration Utility view tables. They are
allowed in the Host Batch Facility or in PC batch.
This example shows how to count all the customers grouped by the state
they are in.
SQL "SELECT STATE-CNT = COUNT(ACCOUNT) &
FROM CUSTOMER-MASTER &
GROUP BY STATE;"
This example gives the total orders grouped by customer account. A three
table JOIN is needed to get the customer name and the product price. The
SUB on the price is needed because the PRICE was configured with a
default of 0 decimal places when in fact the PRICE has an implied 2
decimal places. The Administrator Utility would normally have been used
to configure PRICE with 2 decimal places.
SQL "SELECT TOTAL-ORDERS = SUM(ORDER-QTY &
* SUB(PRICE,1,P8,2)) &
FROM CUSTOMER-MASTER, ORDER-DETAIL, PRODUCT-MASTER &
WHERE CUSTOMER-MASTER.ACCOUNT = ORDER-DETAIL.ACCOUNT AND &
ORDER-DETAIL.PRODUCT-NBR = PRODUCT- MASTER.PRODUCT-NBR &
GROUP BY CUSTOMER;"