SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Language Elements
HP NonStop SQL/MX Reference Manual—540440-003
6-18
Comparable and Compatible Data Types
In addition, if you are using an SQL/MP table that contains FLOAT columns (REAL,
DOUBLE PRECISION) with user default values specified, a similarity check for the
table with the compile-time default value might fail for some values, and
NonStop SQL/MX will recompile the query.
For some queries that use the default value, you might not be able to access a float
column with a default value near the boundary value for Tandem float values.
In this example, you are able to perform an ALTER TABLE statement in SQLCI on an
SQL/MP table to add a float column with a default value near the boundary value for
Tandem float values, but you are unable to use NonStop SQL/MX to insert this default
value into the float column. In SQLCI, create an SQL/MP table:
>>create table tfloat (c1 int, c2 int);
--- SQL operation complete.
>>insert into tfloat (c1) values (10);
--- 1 row(s) inserted.
>>select * from tfloat;
C1 C2
----------- -----------
10 ?
--- 1 row(s) selected.
You can alter the table to add float column with the default value
1.15792089237316189e77:
>>alter table tfloat add column c3 float(54) default -
1.15792089237316189e77;
--- SQL operation complete.
--
-- Float column c3 has not been populated yet.
--
>>select * from tfloat;
C1 C2 C3
----------- ----------- ------------------------
10 ? -0.11579208923731618E+78
But you cannot use NonStop SQL/MX to insert into this table using the default value for
the float column:
Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.0
(c) Copyright 2003 Hewlett-Packard Development Company, LP.
>> insert into $data16.pnlmx.tfloat(c1,c2) values (12, 13);
*** ERROR[8411] A numeric overflow occurred during an arithmetic
computation or data conversion.
--- 0 row(s) inserted.