HP 3000 Manuals

HP ALLBASE/BRW Reference Manual

Using HP ALLBASE/SQL 

To create, compile, and execute an HP ALLBASE/SQL report 

   1.  Specify the report in the same way that you specify any other
       report, except that you give the Source Table name using the SQL
       pathname (including the owner name).

   2.  On the Define Report screen you can enter an SQL Transaction
       Isolation Level.  This entry is optional.  See the "Transaction
       Handling and Locking" section later in this chapter.

   3.  Define the relations and layout just as you would any other
       report.

   4.  Use the Tune Access screen to control how the join will be done
       for multiple tables.  You cannot use Keys for HP ALLBASE/SQL
       tables, but you can use Access Sequence to adjust performance and
       you can use Lock Mode to quarantee that no one changes the table
       while HP ALLBASE/BRW is executing the query.

   5.  Use selection sets and selection conditions to define how you want
       to customize the report at run time.

   6.  Compile the report as you would any other report.

   7.  Execute the report as you would any other report.

Example 

Suppose you are using the SQL database EXDBE (diagrammed in Chapter 1)
and want to create a report that lists user-specified VENDORNAMES and
their corresponding PARTNUMBERS. In other words, the report will show all
part numbers of items sold by a particular vendor.

Specification 

You define the table or tables for an HP ALLBASE/SQL report in much the
same way as you define any other report.  The information on this Define
Table screen tells HP ALLBASE/BRW which HP ALLBASE/SQL tables or views to
use and where they are located.


            

In this case, the source tables are SUPPLYDB.VENDORS and
SUPPLYDB.SUPPLYPRICE. They are both located in EXDBE.PUB for this
example.

Notice that the HP ALLBASE/SQL table name is preceded by the owner name
(SUPPLYDB). Note also that no password has to be specified; refer to the
"Access Authorization" section later in this chapter.

You define the relation between the tables the same way that you define
other relations.  The information on this Define Relation screen tells HP
ALLBASE/BRW the item name (VENDORNUMBER) to use to join the tables shown
on the Define Table screen.


            

The information on this Tune Access screen controls how the joining of
the two tables will be done--either by HP ALLBASE/SQL or HP ALLBASE/BRW.


            

If you use equal access sequence numbers, as shown in this example, the
join is done by HP ALLBASE/SQL. You can also use different access
sequence numbers to have the join done by HP ALLBASE/ BRW. See the
"Performance Considerations" section in this chapter for additional
information about which method may be best for your environment.

Keyed access cannot be specified for SQL tables, so the Key field on this
screen is blank.

If you want to be able to decide on a specific vendor at run time, use a
parameter for the VENDORNAME. You define a parameter as shown in the
example Define Parameter screen shown next.


            
[REV BEG]

In this example, ?VENDORPARM is defined as a single value parameter.  In
addition to defining the parameter, you need to define a selection
condition to equate the VENDORNAME to the ?VENDORPARM parameter.


            
[REV END]

For more information on using BRW parameters, refer to Chapter 6.

When you run HP ALLBASE/BRW you can enter the name of the specific vendor
you want.

Layout 

The items to be printed (VEDORNAME and PARTNUMBER) are specified on the
layout screens for this example.

Compilation 

When this example is compiled, BRWCOMP produces a BRW execution file and
generates the following HP ALLBASE/SQL query:

      SELECT T1.VENDORNAME
         T2.PARTNUMBER
      FROM  SUPPLYDB.VENDORS T1
         SUPPLYDB.SUPPLYPRICE T2
      WHERE (T1.VENDORNUMBER=T2.VENDORNUMBER)
      AND   (T1.VENDORNAME=?VENDORPARM)

Notice that the query above consists of one SELECT statement.  This is
because the same access sequence number was specified for both tables on
the Tune Access screen.  This is the default if the HP ALLBASE/SQL tables
are from the same DBEnvironment and are listed con secutively on the
Define Table screen.

If the access sequence numbers were changed to 1 and 2 respectively, the
following two SELECT statements would be generated:

      SELECT T1.VENDORNAME
         T1.VENDORNUMBER
      FROM  SUPPLYDB.VENDORS T1
      WHERE VENDORNAME=?VENDORPARM

      SELECT T1.VENDORNUMBER
         T1.PARTNUMBER
      FROM  SUPPLYDB.SUPPLYPRICE T1

The two query results are then combined by BRW.

It is important to point out that the above query is not yet the final
query.  It becomes the final query at the time of execution, when the
value of ?VENDORPARM is supplied.

Execution 

The final step in this example is to execute the report.  When the report
is executed it prompts for parameters to be typed in.  The VENDORNAME
supplied is then substituted for ?VENDORPARM, thus creating the final
query and generating the report.

The final query is passed to HP ALLBASE/SQL. Each query is executed by HP
ALLBASE/SQL as a single HP ALLBASE/SQL transaction.  Sorting is always
done by BRW.

MPE/iX 5.0 Documentation