AUTOCOMMIT OFF;-- MODULE  DML106  -- SQL Test Suite, V6.0, Interactive SQL, dml106.sql-- 59-byte ID-- TEd Version #-- AUTHORIZATION FLATER   set schema FLATER;--N temporarily changed authorization to HU, to simplify schema name usage until--N we have permissions; table USIG requires explicit schema name FLATER until--N this is changed back--0   SELECT USER FROM HU.ECCO;  VALUES USER;-- RERUN if USER value does not match preceding AUTHORIZATION comment   ROLLBACK WORK;-- date_time print-- TEST:0599 UNION in views (feature 8) (static)!   CREATE VIEW UUSIG (U1) AS  SELECT C1 FROM USIG UNION SELECT C_1 FROM USIG;-- PASS:0599 If view is created?   COMMIT WORK;   SELECT COUNT(*) FROM UUSIG;-- PASS:0599 If count = 4?   SELECT COUNT(DISTINCT U1) FROM UUSIG;-- PASS:0599 If count = 4?   SELECT COUNT(*) FROM UUSIG WHERE U1 < 0  OR U1 > 3 OR U1 IS NULL;-- PASS:0599 If count = 0?   COMMIT WORK;   CREATE VIEW ABOVE_AVERAGE (COLUMN_1, COLUMN_2, COLUMN_3) AS      SELECT PNUM, BUDGET, CITY        FROM HU.PROJ OUTER_REF        WHERE BUDGET >= (SELECT AVG(BUDGET) FROM HU.PROJ INNER_REF                          WHERE OUTER_REF.CITY = INNER_REF.CITY)     UNION      SELECT 'MAX', MAX(BUDGET), MIN(CITY)        FROM HU.PROJ         WHERE CITY > 'Deale';-- PASS:0599 If view is created?   COMMIT WORK;   SELECT * FROM ABOVE_AVERAGE ORDER BY COLUMN_1;-- PASS:0599 If 4 rows selected with ordered rows and column values: ?-- PASS:0599    MAX  30000  Tampa  ?-- PASS:0599    P2   30000  Vienna ?-- PASS:0599    P3   30000  Tampa  ?-- PASS:0599    P6   50000  Deale  ?   COMMIT WORK;   CREATE VIEW STAFF_DUP AS      SELECT EMPNUM, EMPNAME, GRADE, CITY        FROM HU.STAFF     UNION ALL      SELECT * FROM HU.STAFF3;-- PASS:0599 If view is created?   COMMIT WORK;   SELECT * FROM STAFF_DUP ORDER BY CITY;-- PASS:0599 If 10 rows selected ?-- PASS:0599 If first row contains EMPNUM/CITY values E5 / Akron ?-- PASS:0599 If second row contains EMPNUM/CITY values E5 / Akron ?   SELECT COUNT(*) FROM STAFF_DUP;-- PASS:0599 If count = 10 ?   COMMIT WORK;   CREATE VIEW FOUR_CITIES (C1, C2, C3) AS      SELECT 'P', CITY, 666        FROM HU.PROJ        WHERE BUDGET <> 30000     UNION      SELECT 'S', CITY, 777        FROM HU.STAFF        WHERE EMPNAME <> 'Ed'     UNION      SELECT 'T', CITY, -999        FROM HU.STAFF3        WHERE CITY NOT LIKE 'V%'     UNION      SELECT 'X', CITY, -1        FROM HU.STAFF3        WHERE CITY = 'Vienna';-- PASS:0599 If view is created?   COMMIT WORK;   SELECT C2, C1, C3 FROM FOUR_CITIES ORDER BY C3, C2;-- PASS:0599 If 7 rows selected with ordered rows and column values ?-- PASS:0599    Akron   T     -999  ?-- PASS:0599    Deale   T     -999  ?-- PASS:0599    Vienna  X       -1  ?-- PASS:0599    Deale   P      666  ?-- PASS:0599    Vienna  P      666  ?-- PASS:0599    Deale   S      777  ?-- PASS:0599    Vienna  S      777  ?   SELECT COUNT (*) FROM FOUR_CITIES;-- PASS:0599 If count = 7 ?   SELECT COUNT(*) FROM FOUR_CITIES WHERE C3 > 0;-- PASS:0599 If count = 4 ?   SELECT COUNT(*) FROM FOUR_CITIES WHERE C2 = 'Vienna';-- PASS:0599 If count = 3 ?   COMMIT WORK;--0   DROP VIEW ABOVE_AVERAGE CASCADE;   DROP VIEW ABOVE_AVERAGE ;   COMMIT WORK;--0   DROP VIEW STAFF_DUP CASCADE;   DROP VIEW STAFF_DUP ;   COMMIT WORK;--0   DROP VIEW FOUR_CITIES CASCADE;   DROP VIEW FOUR_CITIES ;   COMMIT WORK;--0   DROP VIEW UUSIG CASCADE;   DROP VIEW UUSIG ;   COMMIT WORK;-- END TEST >>> 0599 <<< END TEST-- *********************************************-- TEST:0601 DATETIME data types (feature 5) (static)!   CREATE TABLE TEMPUS (TDATE DATE, TTIME TIME,--0  TTIMESTAMP TIMESTAMP, TINT1 INTERVAL YEAR TO MONTH,  TTIMESTAMP TIMESTAMP);--0  TINT2 INTERVAL DAY TO SECOND);-- PASS:0601 If table is created?   COMMIT WORK;   INSERT INTO TEMPUS VALUES (  DATE( '1993-08-24'),  TIME( '16:03:00'),  TIMESTAMP( '1993-08-24 16:03:00')--0  , INTERVAL -'1-6' YEAR TO MONTH,--0  INTERVAL '13 0:10' DAY TO SECOND);  );-- PASS:0601 If 1 row is inserted?   SELECT DAY( TDATE)  FROM TEMPUS;-- PASS:0601 If 1 row selected and value is 24?--0   SELECT COUNT(*) FROM TEMPUS--0  WHERE (TTIMESTAMP - TIMESTAMP( '1995-02-24 16:03:00'))--0  YEAR TO MONTH = TINT1;-- PASS:0601 If count = 1?--0   SELECT COUNT(*) FROM TEMPUS--0  WHERE (TTIMESTAMP, TINT1) OVERLAPS--0  (TIMESTAMP( '1995-02-24 16:03:00'), INTERVAL '1-6' YEAR TO MONTH);-- PASS:0601 If count = 0?   ROLLBACK WORK;--0   DROP TABLE TEMPUS CASCADE;   DROP TABLE TEMPUS ;   COMMIT WORK;-- END TEST >>> 0601 <<< END TEST-- *********************************************-- TEST:0611 FIPS sizing, DATETIME data types (static)!   CREATE TABLE TSFIPS (  FIPS1 TIME,  FIPS2 TIMESTAMP--0  , FIPS3 INTERVAL YEAR (2) TO MONTH,--0  FIPS4 INTERVAL DAY (2) TO SECOND (6));  );-- PASS:0611 If table is created?   COMMIT WORK;   INSERT INTO TSFIPS VALUES (  TIME( '16:03:00'),  TIMESTAMP( '1996-08-24 16:03:00.999999')--0  ,INTERVAL -'99-6' YEAR (2) TO MONTH,--0  INTERVAL '99 0:10:00.999999' DAY (2) TO SECOND (6));  );-- PASS:0611 If 1 row is inserted?   SELECT SECOND( FIPS2)  * 1000000 - 999990 FROM TSFIPS;-- PASS:0611 If 1 row selected and value is 9?--0   SELECT YEAR( FIPS3),--0  MONTH( FIPS3)--0  FROM TSFIPS;-- PASS:0611 If 1 row selected and values are -99 and -6?--0   SELECT DAY( FIPS4),--0  SECOND( FIPS4) * 1000000 - 999990--0  FROM TSFIPS;-- PASS:0611 If 1 row selected and values are 99 and 9?   ROLLBACK WORK;--0   DROP TABLE TSFIPS CASCADE;   DROP TABLE TSFIPS ;   COMMIT WORK;-- END TEST >>> 0611 <<< END TEST-- *********************************************-- TEST:0613 <datetime value function> (static)!   CREATE TABLE TSSMALL (  SMALLD DATE,  SMALLT TIME,  SMALLTS TIMESTAMP);-- PASS:0613 If table is created?   COMMIT WORK;   INSERT INTO TSSMALL VALUES (  CURRENT_DATE, CURRENT_TIME, CURRENT_TIMESTAMP);-- PASS:0613 If 1 row is inserted?   SELECT COUNT(*)FROM TSSMALL WHEREYEAR( SMALLD) = YEAR( SMALLTS);-- PASS:0613 If count = 1?   SELECT COUNT(*)FROM TSSMALL WHEREMONTH( SMALLD) = MONTH( SMALLTS);-- PASS:0613 If count = 1?   SELECT COUNT(*)FROM TSSMALL WHEREDAY( SMALLD) = DAY( SMALLTS);-- PASS:0613 If count = 1?   SELECT COUNT(*)FROM TSSMALL WHEREHOUR( SMALLT) = HOUR( SMALLTS);-- PASS:0613 If count = 1?   SELECT COUNT(*)FROM TSSMALL WHEREMINUTE( SMALLT) = MINUTE( SMALLTS);-- PASS:0613 If count = 1?   SELECT COUNT(*)FROM TSSMALL WHERESECOND( SMALLT) -SECOND( SMALLTS) > -1AND SECOND( SMALLT) -SECOND( SMALLTS) < 1;-- PASS:0613 If count = 1?   ROLLBACK WORK;--0   DROP TABLE TSSMALL CASCADE;   DROP TABLE TSSMALL ;   COMMIT WORK;-- END TEST >>> 0613 <<< END TEST-- *********************************************-- TEST:0615 DATETIME-related SQLSTATE codes (static)!   CREATE TABLE TSERR (--0  BADINT INTERVAL YEAR (2) TO MONTH,  BADDATE DATE);-- PASS:0615 If table is created?   COMMIT WORK;--0   INSERT INTO TSERR VALUES (--0  INTERVAL '0-11' YEAR TO MONTH,--0  DATE( '9999-01-01') + INTERVAL '1-00' YEAR TO MONTH);-- PASS:0615 If ERROR, datetime field overflow, 0 rows inserted?   INSERT INTO TSERR VALUES (--0  INTERVAL '9999-11' YEAR TO MONTH,  DATE( '1984-01-01'));-- PASS:0615 If ERROR, interval field overflow, 0 rows inserted?--0   INSERT INTO TSERR VALUES (--0  INTERVAL '1-11' YEAR TO MONTH,--0  CAST ('DATE ''1993-02-30''' AS DATE));-- PASS:0615 If ERROR, invalid datetime format, 0 rows inserted?--0   INSERT INTO TSERR VALUES (--0  INTERVAL '1-11' YEAR TO MONTH,--0  CAST ('1993-02-30' AS DATE));-- PASS:0615 If ERROR, invalid datetime format, 0 rows inserted?   ROLLBACK WORK;--0   DROP TABLE TSERR CASCADE;   DROP TABLE TSERR ;   COMMIT WORK;-- END TEST >>> 0615 <<< END TEST-- *************************************************////END-OF-MODULE