Problem Note 50236: An "out of range" error occurs when you insert a BIGINT value into a Netezza table
When using a DATA Step or SQL procedure to insert a BIGINT value greater than 922337203685477580 into a Netezza table, you might see the following ERROR message written to the SAS log:
ERROR: CLI execute error: ERROR: int8 value out of range: "9223372036854775888"
The table is created, but when it is accessed, this message is issued:
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
To work around the error, either do not use values larger than 19 digits, or else you can convert the values to character, as follows:
proc sql;
create table nz.newtable as
select put(t1.i, 20.) as new_i from work.test t1 ;
quit;
Click the Hot Fix tab in this note to access the hot fix for this issue.
Operating System and Release Information
| SAS System | SAS/ACCESS Interface to Netezza | 64-bit Enabled AIX | 9.22_M2 | 9.4 | 9.2 TS2M3 | 9.4 TS1M0 |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
this is the Log showing the code that was used to illustrate the behavior reported
148 proc sql;
149 connect to netezza (user=dbitest pass=XXXXXXX server=enzee database=test);
150 execute (drop table test_bigint1, test_bigint2, test_bigint3, test_bigint4) by netezza;
151 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.12 seconds
cpu time 0.00 seconds
152
153 proc sql;
154 connect to netezza (user=dbitest pass=XXXXXXX server=enzee database=test);
155 execute (create table test_bigint1 (col1 bigint)) by netezza;
156 execute (insert into test_bigint1 values (9223372036854775806)) by netezza;
157 execute (insert into test_bigint1 values (9223372036854775888)) by netezza;
ERROR: CLI execute error: ERROR: int8 value out of range: "9223372036854775888"
158 execute (create table test_bigint2 (col1 bigint)) by netezza;
159 execute (insert into test_bigint2 values (92233720368547)) by netezza;
160 execute (insert into test_bigint2 values (92233720368555)) by netezza;
161 execute (create table test_bigint3 (col1 bigint)) by netezza;
162 execute (insert into test_bigint3 values (922337203685477)) by netezza;
163 execute (insert into test_bigint3 values (922337203690000)) by netezza;
164 execute (create table test_bigint4 (col1 bigint)) by netezza;
165 execute (insert into test_bigint4 values (9223372036854775)) by netezza;
166 execute (insert into test_bigint4 values (9223372036859999)) by netezza;
167 execute (create table test_bigint5 (col1 bigint)) by netezza;
ERROR: CLI execute error: ERROR: Relation 'TEST_BIGINT5' already exists
168 execute (insert into test_bigint5 values (9223372036854775806)) by netezza;
169 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 1.95 seconds
cpu time 0.01 seconds
170
171 proc sql;
172 select * from nzunique.test_bigint1;
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds
173 quit;
174
175
176 proc sql;
177 select * from nzunique.test_bigint2;
178 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.11 seconds
cpu time 0.01 seconds
179
180 proc sql;
181 select * from nzunique.test_bigint3;
182 quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.10 seconds
cpu time 0.01 seconds
183
184 proc sql;
185 select * from nzunique.test_bigint4;
ERROR: CLI cursor extended fetch error: Numeric value out of range : Numeric value out of range
186 quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.01 seconds
187
188 proc sql;
189 select * from nzunique.test_bigint5;
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.09 seconds
cpu time 0.00 seconds
190 quit;
===========the following error occurs when trying to open in Windows Explorer===============================
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
ERROR: Invalid Operation.
ERROR: Termination due to Floating Point Exception
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2013-07-05 12:35:21 |
| Date Created: | 2013-06-20 10:35:59 |
