| 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> |
