SQL Programming Manual for TAL

Host Variables and Parameters
HP NonStop SQL Programming Manual for TAL—527887-001
2-15
Using Host Variables
•
Using SETSCALE with the UPDATE statement
A program updates the PARTS.PRICE column for a disk controller to $158.34. The
value is multiplied by 100 and stored in the host variable :HOSTVAR2.
! Assign 15834 to :hostvar2
EXEC SQL
UPDATE =parts SET parts.price = SETSCALE (:hostvar2, 2)
WHERE parts.partdesc = "disk controller" ;
•
Using SETSCALE with the SELECT statement for retrieving a value
A program retrieves the value in the PARTS.PRICE column for a disk controller and
stores the value in the host variable :HOSTVAR3. The value has a scale of 2.
EXEC SQL
SELECT parts.price INTO SETSCALE ( :hostvar3, 2 )
FROM =parts
WHERE parts.partdesc = "disk controller" ;
IF hostvar3/100 > 50000 THEN
! (print message that manager approval
! is required for purchase )
If the price of the disk controller is $60,000.00 in the database, :HOSTVAR3 has the
value 6,000,000 on output.
•
Using SETSCALE with the SELECT statement for comparisons
A program retrieves the part description for the part with a price of $999.50. The price
value is stored in the host variable :HOSTVAR4 and supplied to SQL in the search
condition. The retrieved value is stored in the host variable :HVSTORE.
! Assign 99950 to :hostvar4
EXEC SQL
SELECT parts.partdesc INTO :hvstore
FROM =parts
WHERE parts.price = SETSCALE ( :hostvar4, 2 );
Using Date-Time and INTERVAL Data Types as Host Variables
NonStop SQL provides a set of special data types for specifying:
•
Date-time values including separate date and time values
•
Intervals or durations of time
Table 2-3 shows the date-time and INTERVAL data types. For more information on the
complete syntax, see the SQL/MP Reference Manual.