dml083.out

derby database source code.good for you.

ij> AUTOCOMMIT OFF;
ij> -- MODULE DML083
-- SQL Test Suite, V6.0, Interactive SQL, dml083.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."
-- NO_TEST:0496 SQLSTATE 22002: data exception/null, value, no indic.!
-- Testing indicators
-- *********************************************
-- TEST:0498 SQLSTATE 22001: data exception/string right trunc.!
   INSERT INTO HU.STAFF 
         VALUES ('E6','Earl Brown',11,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?
   INSERT INTO HU.STAFF 
         VALUES ('E7','Ella Brown',12,'Claggetsville Maryland');
ERROR 22001: A truncation error was encountered trying to shrink CHAR 'Claggetsville Maryland' to length 15.
ij> -- PASS:0498 If ERROR, data exception/string right trunc.?
-- PASS:0498 If 0 rows inserted OR SQLSTATE = 22001 OR SQLCODE < 0?
--O   SELECT COUNT(*)
   SELECT *
         FROM HU.STAFF;
EM&|EMPNAME             |GRADE|CITY           
----------------------------------------------
E1 |Alice               |12   |Deale          
E2 |Betty               |10   |Vienna         
E3 |Carmen              |13   |Vienna         
E4 |Don                 |12   |Deale          
E5 |Ed                  |13   |Akron          
ij> -- PASS:0498 If count = 5?
-- restore
   ROLLBACK WORK;
ij> -- END TEST >>> 0498 <<< END TEST
-- *********************************************
-- TEST:0500 SQLSTATE 01003: warning/null value elim. in set function!
-- setup
   DELETE FROM HU.HH;
0 rows inserted/updated/deleted
ij> INSERT INTO HU.HH 
         VALUES (3);
1 row inserted/updated/deleted
ij> INSERT INTO HU.HH 
         VALUES (NULL);
1 row inserted/updated/deleted
ij> SELECT AVG(SMALLTEST)
         FROM HU.HH;
1     
------
3     
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
-- setup
   UPDATE HU.STAFF 
         SET GRADE = NULL
         WHERE GRADE = 13;
2 rows inserted/updated/deleted
ij> SELECT AVG(GRADE)
         FROM HU.STAFF
         WHERE CITY = 'Vienna';
1           
------------
10.0000     
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
   SELECT SUM(DISTINCT GRADE)
         FROM HU.STAFF;
1        
---------
22       
WARNING 01003: Null values were eliminated from the argument of a column function.
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
   INSERT INTO HU.HH 
         SELECT MAX(GRADE) 
               FROM HU.STAFF;
1 row inserted/updated/deleted
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
   DELETE FROM HU.HH 
         WHERE SMALLTEST < (SELECT MIN(GRADE)
                                  FROM HU.STAFF
                                  WHERE CITY = 'Vienna');
1 row inserted/updated/deleted
ij> -- PASS:0500 If WARNING, null value eliminated in set function?
-- PASS:0500 OR SQLSTATE = 01003?
   SELECT CITY, COUNT(DISTINCT GRADE) 
         FROM HU.STAFF
         GROUP BY CITY
         ORDER BY CITY DESC;
CITY           |2          
---------------------------
Vienna         |1          
WARNING 01003: Null values were eliminated from the argument of a column function.
Deale          |1          
Akron          |0          
ij> -- PASS:0500 If 3 rows are selected with the following order?
-- PASS:0500     CITY    COUNT(DISTINCT GRADE)?
-- PASS:0500   'Vienna'          1?
-- PASS:0500   'Deale'           1?
-- PASS:0500   'Akron'           0?
-- PASS:0500 OR SQLSTATE = 01003?
-- restore
   ROLLBACK WORK;
ij> -- END TEST >>> 0500 <<< END TEST
-- *********************************************
-- NO_TEST:0501 SQLSTATE 01004: warning/string right truncation!
-- Testing host variables
-- *************************************************////END-OF-MODULE
;
ij>