ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML181  

-- SQL Test Suite, V6.0, Interactive SQL, dml181.sql
-- 59-byte ID
-- TEd Version #

-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --O   SELECT USER FROM HU.ECCO;
  VALUES USER;
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
FLATER                                                                                                                          
ij> -- RERUN if USER value does not match preceding AUTHORIZATION comment
   ROLLBACK WORK;
ij> -- date_time print

-- TEST:0894 FIPS sizing, length of column lists >= 750!

   CREATE TABLE LONG_NAMED_PEOPLE (
     FIRSTNAME VARCHAR (373) NOT NULL,
     LASTNAME VARCHAR (373) NOT NULL,
     AGE INT,
     PRIMARY KEY (FIRSTNAME, LASTNAME));
0 rows inserted/updated/deleted
ij> -- PASS:0894 If table created successfully?

   COMMIT WORK;
ij> CREATE TABLE ORDERS (
     FIRSTNAME VARCHAR (373),
     LASTNAME VARCHAR (373),
     TITLE VARCHAR (80),
     COST NUMERIC(5,2),
	FOREIGN KEY (FIRSTNAME, LASTNAME)
	REFERENCES LONG_NAMED_PEOPLE);
0 rows inserted/updated/deleted
ij> -- PASS:0894 If table created successfully?

   COMMIT WORK;
ij> CREATE VIEW PEOPLE_ORDERS AS
--O     SELECT * FROM LONG_NAMED_PEOPLE  JOIN ORDERS 
     SELECT a.firstname, a.lastname, age, cost FROM LONG_NAMED_PEOPLE a JOIN ORDERS
     ON (a.FIRSTNAME=ORDERS.FIRSTNAME and a.LASTNAME=ORDERS.LASTNAME);
0 rows inserted/updated/deleted
ij> -- PASS:0894 If view created successfully?

   COMMIT WORK;
ij> INSERT INTO LONG_NAMED_PEOPLE VALUES (
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaa', 
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbb',
20);
1 row inserted/updated/deleted
ij> -- PASS:0894 If 1 row inserted successfully?

   INSERT INTO LONG_NAMED_PEOPLE VALUES (
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccc',
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddd',
25);
1 row inserted/updated/deleted
ij> -- PASS:0894 If 1 row inserted successfully?

   INSERT INTO ORDERS VALUES (
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaa',
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbb',
'Gidget Goes Skiing',
29.95);
1 row inserted/updated/deleted
ij> -- PASS:0894 If 1 row inserted successfully?

   INSERT INTO ORDERS VALUES (
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' ||
'aaaaaaaaaaaaaaaaaaaaaaaaa',
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb' ||
'bbbbbbbbbbbbbbbbbbbbbbbbb',
'Barney Goes Hawaiian',
19.95);
1 row inserted/updated/deleted
ij> -- PASS:0894 If 1 row inserted successfully?

   INSERT INTO ORDERS VALUES (
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc' ||
'ccccccccccccccccccccccccccccccc',
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd' ||
'ddddddddddddddddddddddddddddddd',
'Invasion of the Smurfs',
9.95);
1 row inserted/updated/deleted
ij> -- PASS:0894 If 1 row inserted successfully?

   SELECT FIRSTNAME, LASTNAME, AVG(COST)
     FROM PEOPLE_ORDERS
     GROUP BY LASTNAME, FIRSTNAME
     ORDER BY LASTNAME, FIRSTNAME;
FIRSTNAME                                                                                                                       |LASTNAME                                                                                                                        |3          
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa&|bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb&|24.9500    
ccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccccc&|ddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddddd&|9.9500     
ij> -- PASS:0894 If 2 rows are returned in the following order?
-- NOTE:  Columns c1 and c2 are 373 characters each!
--                   c1           c2            c3
--                   ==           ==            ==
-- PASS:0894 If  aaaaaaaa....    bbbbbbbb....  24.95 (+ or - 0.01)?
-- PASS:0894 If  cccccccc....    dddddddd....   9.95 (+ or - 0.01)?

   COMMIT WORK;
ij> --O   DROP TABLE ORDERS CASCADE;
   drop view people_orders;
0 rows inserted/updated/deleted
ij> DROP TABLE ORDERS ;
0 rows inserted/updated/deleted
ij> -- PASS:0894 If table dropped successfully?

   COMMIT WORK;
ij> --O   DROP TABLE LONG_NAMED_PEOPLE CASCADE;
   DROP TABLE LONG_NAMED_PEOPLE ;
0 rows inserted/updated/deleted
ij> -- PASS:0894 If table dropped successfully?

   COMMIT WORK;
ij> -- END TEST >>> 0894 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>