[USflag] The American Programmer [USflag]


Home
Books on Mainframe Programming
Mainframe Manuals and Tutorials
System Abend codes, Sqlcodes, VSAM/QSAM codes
Everything about the IBM AS/400 Midrange Computer
Everything about CICS
Everything about COBOL
Everything about DB2 and SQL
  DB2/SQL Singleton Select embedded in a COBOL program
  DB2/SQL Add Change Delete program
  DB2/SQL program to read table with cursor
  DB2/SQL load table program
  DB2/SQL program to read a table randomly
  DB2/SQL bare bones skeleton program
  DB2/SQL program to insert a row into a table
  DB2/SQL program to update a row
  DB2/SQL program for handling variable length column
  DB2/SQL program for handling column with null indicator
  Embedded SQL. Short tutorial.
  Examples of DCLGEN. Short tutorial.
  Examples of SQL Joins. Short tutorial.
  Examples of DB2 View. Short tutorial.
  Just Enough SQL/QMF/SPUFI To Be Dangerous
  Sample tables STAFF, ORG, EMP and DEPT
  QMF Procedures Written in REXX
  Using REXX Subroutines with the QMF Calc panel
  Using QMF to Execute Your SQL
  QMF Cheat Sheet. Edit codes. QMF Commands, Text Variables, Usage Codes
  Executing SQL in a batch job
  Using Spufi to Execute Your SQL
  Books on DB2 and SQL
  DB2 and SQL, Structured Query Language manuals
  SQLCODES and Their Causes
Everything about IMS
Everything about Java and JavaScript
Everything about JCL and JES
Everything about REXX
Everything about zOS, VSAM, Tivoli, Assembler
Everything about TSO, ISPF, Spufi
Site Map and Site Search

           Home   > DB2   > DB2/SQL View

DB2 SQL View

Description of the example tables STAFF, ORG, EMP and DEPT

-- some examples of views
-- SIMPLE VIEW, WITH WHERE CLAUSE                               
CREATE VIEW EMPV                                                
AS                                                              
SELECT EMPNO, LASTNAME, SALARY, COMM                            
FROM DSN8610.EMP                                                
WHERE SALARY > 30000                                            
;                                                               
                                                                
SELECT * FROM EMPV;                                             
                                                                
                                                                
-- VIEW CHANGING COLUMN NAME                                    
CREATE VIEW EMPV2                                               
AS                                                              
SELECT EMPNO, LASTNAME, SALARY, COMM  AS COMMISH                
FROM DSN8610.EMP                                                
WHERE SALARY > 30000                                            
;                                                               
     

                                                                  
SELECT COMMISH FROM EMPV2;                                        
--SELECT COMM    FROM EMPV2; -- THIS WON'T WORK                   
                                                                  
                                                                  
-- VIEW THAT CHANGES ALL COLUMN NAMES, ANOTHER WAY                
CREATE VIEW EMPV3                                                 
      (EM,    LA,       SA,     CO)                               
AS                                                                
SELECT EMPNO, LASTNAME, SALARY, COMM                              
FROM DSN8610.EMP                                                  
WHERE SALARY > 30000                                              
;                                                                 
                                                                  
SELECT CO      FROM EMPV3;                                        
                                  


-- EXAMPLE OF AN INLINE VIEW                                        
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO, E.LASTNAME                    
FROM DEPT D                                                         
   INNER JOIN                                                       
       (SELECT WORKDEPT, LASTNAME FROM EMP) AS E                    
   ON D.DEPTNO = E.WORKDEPT                                         
;                                                                   
                                                                    
                                                                    
-- EXAMPLE OF AN INLINE VIEW                                        
SELECT D.DEPTNO, D.DEPTNAME, D.MGRNO, E.LA                          
FROM DEPT D                                                         
   INNER JOIN                                                       
       (SELECT WORKDEPT, LASTNAME AS LA FROM EMP) AS E              
   ON D.DEPTNO = E.WORKDEPT                                         
;                                                                   
                                                                                                    



Top of Page

Your email and other personal information will not be given to anyone and will be used only to communicate with you about your order.

[Books Computer]

Home Books for Computer Professionals Privacy Terms |
Site Map and Site Search Programming Manuals and Tutorials The REXX Files Top of Page |