SQL/MX 2.x Reference Manual (G06.24+, H06.03+)

Embedded-Only SQL/MX Statements
HP NonStop SQL/MX Reference Manual—523725-004
3-63
Considerations for IF Statement
Considerations for IF Statement
SQL Statements in the List
The restrictions for which SQL statements can be used in a list are the same as the
restrictions for the compound statement. Transactional SQL statements BEGIN
WORK, COMMIT WORK, ROLLBACK WORK, and SET TRANSACTION cannot be
used in IF statements. UPDATE STATISTICS and CONTROL statements cannot be
used in IF statements.
SELECT INTO (retrieving only one row) can be used in a list. Cursors are not allowed
in compound statements. However, rowsets can be used within compound statements
to retrieve multiple rows from database tables.
C Example of IF Statement
•
These INSERT and SELECT statements execute sequentially for new orders.
Otherwise, the SELECT statement returns information about the current customer:
...
EXEC SQL
BEGIN
IF :hv_new_ordernum <> 0
THEN
INSERT INTO SALES.ORDERS
(ORDERNUM, ORDER_DATE, DELIV_DATE, SALESREP, CUSTNUM)
VALUES (:hv_new_ordernum, :hv_orderdate, :hv_delivdate,
:hv_salesrep, :hv_custnum);
SELECT CUSTNUM, CUSTNAME, STREET, CITY, STATE, POSTCODE
INTO :hv_custnum, :hv_custname,
:hv_street, :hv_city, :hv_state, :hv_postcode
FROM SALES.CUSTOMER
WHERE CUSTNUM = :hv_custnum;
ELSE
SELECT CUSTNUM, CUSTNAME, STREET, CITY, STATE, POSTCODE
INTO :hv_custnum, :hv_custname,
:hv_street, :hv_city, :hv_state, :hv_postcode
FROM SALES.CUSTOMER
WHERE CUSTNUM = :hv_current_custnum;
END IF;
END;
...