SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
S-47
Examples—SETSCALE
For example, if the program is representing a price of $123.45, the program should
assign 12345 to host-var and use SETSCALE to specify a scale of two.
If the value is being retrieved from the database through a SELECT operation, SQL
assigns a value that allows for the scale to host-var. For example, if SQL is storing
123.45, the value 12345 is returned to host-var when the program specifies
SETSCALE with a scale of two in the SELECT statement.
To use SETSCALE in an expression, you must apply SETSCALE to each operand
individually rather than to the result of the expression. For example, this expression
adds two prices with a scale of two decimal places:
SETSCALE (:PRICE1, 2) + SETSCALE (:PRICE2, 2)
Examples—SETSCALE

This C program fragment uses SETSCALE with an INSERT to create a new row
with the value 98.34 in the PARTS.PRICE column after storing the value in host
variable :HV1. The value is multiplied by 100 for storing as a whole number.
HV1 = 9834;
EXEC SQL INSERT INTO =PARTS (PRICE)
VALUES ( SETSCALE (:hv1, 2) );

This C program fragment uses SETSCALE with UPDATE to change a value in the
PARTS.PRICE column to $158.34. The value is multiplied by 100 and stored in
host variable :HV2.
HV2 = 15834;
EXEC SQL UPDATE PARTS SET PARTS.PRICE = SETSCALE (:hv2, 2)
WHERE PARTS.PARTDESC = "DISK CONTROLLER";

This C program fragment uses SETSCALE with SELECT to retrieve the value for a
disk controller from the PARTS.PRICE column and stores the value in host variable
:HV3. The value has a scale of two.
EXEC SQL SELECT PARTS.PRICE INTO SETSCALE ( :HV3, 2 )
FROM =PARTS
WHERE PARTS.PARTDESC = "DISK CONTROLLER";

This C program fragment uses SETSCALE with SELECT to retrieve the part
description for the part with a price of $999.50. The price value is stored in host
variable :HV4 and supplied to SQL in the search condition. The retrieved value is
stored in host variable :HVSTORE.
HV4 = 99950;
EXEC SQL SELECT PARTS.PARTDESC INTO :HVSTORE
FROM =PARTS
WHERE PARTS.PRICE = SETSCALE ( :hv4, 2 );