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 WHERE
YEAR( SMALLD) = YEAR( SMALLTS);
-- PASS:0613 If count = 1?

   SELECT COUNT(*)
FROM TSSMALL WHERE
MONTH( SMALLD) = MONTH( SMALLTS);
-- PASS:0613 If count = 1?

   SELECT COUNT(*)
FROM TSSMALL WHERE
DAY( SMALLD) = DAY( SMALLTS);
-- PASS:0613 If count = 1?

   SELECT COUNT(*)
FROM TSSMALL WHERE
HOUR( SMALLT) = HOUR( SMALLTS);
-- PASS:0613 If count = 1?

   SELECT COUNT(*)
FROM TSSMALL WHERE
MINUTE( SMALLT) = MINUTE( SMALLTS);
-- PASS:0613 If count = 1?

   SELECT COUNT(*)
FROM TSSMALL WHERE
SECOND( SMALLT) -
SECOND( SMALLTS) > -1
AND 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