ij> AUTOCOMMIT OFF;
ij> -- MODULE  DML162  

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

-- AUTHORIZATION FLATER
   set schema FLATER;
0 rows inserted/updated/deleted
ij> --0   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:0863 <joined table> directly contained in cursor,view!

   CREATE VIEW BLIVET (CITY, PNUM, EMPNUM, EMPNAME, GRADE,
      HOURS, PNAME, PTYPE, BUDGET) AS
--0      HU.STAFF NATURAL JOIN HU.WORKS NATURAL JOIN HU.PROJ;
	  SELECT HU.PROJ.CITY, HU.PROJ.PNUM, HU.STAFF.EMPNUM, EMPNAME, GRADE, HOURS, PNAME, PTYPE, BUDGET
      FROM HU.STAFF JOIN HU.WORKS ON (HU.STAFF.EMPNUM=HU.WORKS.EMPNUM) JOIN HU.PROJ ON (HU.PROJ.PNUM=HU.WORKS.PNUM AND HU.PROJ.CITY=HU.STAFF.CITY)
	  ;
0 rows inserted/updated/deleted
ij> -- PASS:0863 If view created successfully?

   COMMIT WORK;
ij> SELECT COUNT(*) 
     FROM BLIVET WHERE EMPNUM = 'E1';
1          
-----------
3          
ij> -- PASS:0863 If COUNT = 3?

   SELECT COUNT(*) 
     FROM BLIVET WHERE EMPNUM <> 'E1';
1          
-----------
3          
ij> -- PASS:0863 If COUNT = 3?

   SELECT * FROM HU.STAFF LEFT OUTER JOIN HU.WORKS
      ON (HU.STAFF.EMPNUM=HU.WORKS.EMPNUM);
EM&|EMPNAME             |GRADE|CITY           |EMP&|PNUM|HOURS 
---------------------------------------------------------------
E1 |Alice               |12   |Deale          |E1  |P1  |40    
E1 |Alice               |12   |Deale          |E1  |P2  |20    
E1 |Alice               |12   |Deale          |E1  |P3  |80    
E1 |Alice               |12   |Deale          |E1  |P4  |20    
E1 |Alice               |12   |Deale          |E1  |P5  |12    
E1 |Alice               |12   |Deale          |E1  |P6  |12    
E2 |Betty               |10   |Vienna         |E2  |P1  |40    
E2 |Betty               |10   |Vienna         |E2  |P2  |80    
E3 |Carmen              |13   |Vienna         |E3  |P2  |20    
E4 |Don                 |12   |Deale          |E4  |P2  |20    
E4 |Don                 |12   |Deale          |E4  |P4  |40    
E4 |Don                 |12   |Deale          |E4  |P5  |80    
E5 |Ed                  |13   |Akron          |NULL|NULL|NULL  
ij> -- PASS:0863 If 13 rows are returned?

   COMMIT WORK;
ij> --0   DROP VIEW BLIVET CASCADE;
   DROP VIEW BLIVET ;
0 rows inserted/updated/deleted
ij> COMMIT WORK;
ij> -- END TEST >>> 0863 <<< END TEST
-- *********************************************
-- *************************************************////END-OF-MODULE
;
ij>