dml091.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML091  
-- SQL Test Suite, V6.0, Interactive SQL, dml091.sql
-- 59-byte ID
-- TEd Version #
-- AUTHORIZATION SCHANZLE
   set schema SCHANZLE;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
SCHANZLE                                                                                                                        
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
-- date_time print
-- NOTE Direct support for SQLCODE or SQLSTATE is not required
-- NOTE    in Interactive Direct SQL, as defined in FIPS 127-2.
-- NOTE   ********************* instead ***************************
-- NOTE If a statement raises an exception condition,
-- NOTE    then the system shall display a message indicating that
-- NOTE    the statement failed, giving a textual description
-- NOTE    of the failure.
-- NOTE If a statement raises a completion condition that is a
-- NOTE    "warning" or "no data", then the system shall display
-- NOTE    a message indicating that the statement completed,
-- NOTE    giving a textual description of the "warning" or "no data."
-- TEST:0497 SQLSTATE 22003: data exception/numeric val.range 2!
-- setup
   DELETE FROM HU.P1;
0 rows inserted/updated/deleted
ij> DELETE FROM FOUR_TYPES;
0 rows inserted/updated/deleted
ij> INSERT INTO HU.P1 
         VALUES (100000);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(1,0).
ij> -- PASS:0497 If 1 row is inserted?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0? 
   INSERT INTO HU.P1 
         VALUES (-1000000);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(1,0).
ij> -- PASS:0497 If 1 row is inserted?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows inserted OR SQLSTATE = 22003 OR SQLCODE < 0?
   INSERT INTO HU.P1 
         VALUES (-9);
1 row inserted/updated/deleted
ij> -- PASS:0497 If 1 row is inserted?
   INSERT INTO HU.P1 
         VALUES (9);
1 row inserted/updated/deleted
ij> -- PASS:0497 If 1 row is inserted?
   UPDATE HU.P1 
         SET NUMTEST = NUMTEST + 100000
      WHERE NUMTEST = 9;
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(1,0).
ij> -- PASS:0497 If 1 row is updated?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows updated OR SQLSTATE = 22003 OR SQLCODE < 0?
   UPDATE HU.P1 SET NUMTEST =
       ((1 + NUMTEST) * 100000)
     WHERE NUMTEST = 100009
    OR    NUMTEST IN (SELECT GRADE - 4 FROM HU.STAFF);
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(1,0).
ij> -- PASS:0497 If 1 row is updated?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows updated OR SQLSTATE = 22003 OR SQLCODE < 0?
   UPDATE HU.P1
         SET NUMTEST = NUMTEST * 200000
         WHERE NUMTEST = -9;
ERROR 22003: The resulting value is outside the range for the data type DECIMAL/NUMERIC(1,0).
ij> -- PASS:0497 If 1 row is updated?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows updated OR SQLSTATE = 22003 OR SQLCODE < 0?
-- setup
   INSERT INTO FOUR_TYPES 
         VALUES (1,'X',11112222.00,.000003E-25);
1 row inserted/updated/deleted
ij> SELECT T_DECIMAL / .000000001
         FROM FOUR_TYPES 
         WHERE T_CHAR = 'X';
1                                 
----------------------------------
11112222000000000.00000000000000  
ij> -- PASS:0497 If 1 row is selected and T_DECIMAL = 1.1112222E+16 ?
-- PASS:0497 OR ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows selected OR SQLSTATE = 22003 OR SQLCODE < 0?
-- NOTE:0497 If the following values are too large (not supported),
-- NOTE:0497  use TEd to decrease them to maximum allowed.
   INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> INSERT INTO FOUR_TYPES (T_REAL) VALUES (-1.555222E+38);
1 row inserted/updated/deleted
ij> -- NOTE:0497 If we have not inserted enough big values into FOUR_TYPES,
-- NOTE:0497 to cause an ERROR on the SELECT SUM(T_REAL) below, then
-- NOTE:0497 use TEd to enlarge the above values for T_REAL to the
-- NOTE:0497 maximum allowed by your implementation.
-- NOTE:0497 If that is not enough, add more INSERTs.
   SELECT SUM(T_REAL) FROM FOUR_TYPES;
1            
-------------
ERROR 22003: The resulting value is outside the range for the data type REAL.
ij> -- PASS:0497 If ERROR, data exception/numeric value out of range?
-- PASS:0497 OR 0 rows selected OR SQLSTATE = 22003 OR SQLCODE < 0?
   ROLLBACK WORK;
ij> -- END TEST >>> 0497 <<< END TEST
-- *************************************************////END-OF-MODULE
;
ij>