SQL/MX Queuing and Publish/Subscribe Services

Examples
HP NonStop SQL/MX Queuing and Publish/Subscribe Services—523734-002
B-8
Scenario 4. Fetching Contract Data
Normally, in embedded SQL, a SELECT INTO statement is restricted to retrieving a
single row (or, in the case of a rowset, the number of rows that fit into the rowset).
Otherwise, if more than one row (or more than the number of rows that fit into a
rowset) qualifies for retrieval, SQL/MX returns an error stating that a row subquery or
SELECT INTO statement cannot return more than one row.
In contrast, when a SELECT INTO statement accesses a stream, SQL/MX also
retrieves only a single row (or only the number of rows that fit into a rowset) but does
not return an error if more than the number of expected rows qualify for retrieval.
In the case of an embedded DELETE or an UPDATE within a SELECT INTO
statement, the SQL/MX compiler ensures that the number of rows deleted or updated
does not exceed the number of rows expected even if more rows qualify for retrieval.
This restriction also applies when the result of an embedded DELETE or an UPDATE
is joined with another table.
Scenario 4. Fetching Contract Data
An application that processes invoices often receives an invoice along with relevant
contract information. Therefore, the information fetched must include columns from
both the invoice table and the contract table.
The Invoices Table
The invoice table is defined as:
CREATE TABLE pubs.invoices
(contractnbr INT,
amount INT,
priority INT);
If you do not specify a PRIMARY KEY or CLUSTERING KEY for an SQL/MP table,
SQL/MX stores rows in the order of the SYSKEY.
The Contracts Table
Suppose that the contract table is defined as:
CREATE TABLE pubs.contracts
(contractnbr INT NOT NULL,
amount INT,
PRIMARY KEY (contractnbr));
If you specify a PRIMARY KEY for an SQL/MP table, SQL/MX stores rows in the order
of the values of the primary key.