With the SELECT statement, you can specify the name of each column you want to retrieve from a table. You can name one or more columns from a table or view, or you can select all the columns. Each SELECT statement can select information from several tables.
You can use the DISTINCT keyword to eliminate duplicate information if you are selecting data from multiple tables.
QMF displays selected data according to the default edit code for the data type.
No more than one SELECT statement can be used in a query that includes other SQL statements.
Selecting every column from a table
In this statement, tablename is the name of the table or view you are searching. For example, this statement returns all the columns in Q.ORG:
SELECT * FROM tablename
SELECT * FROM Q.ORG
SELECT * FROM Q.STAFF WHERE DEPT = 10
Selecting columns from a table
To select columns from a table, enter SELECT, followed by the exact names of the columns in the order (left to right) in which you want them in your report. Separate column names by a comma.
SELECT DEPTNAME, DEPTNUMB FROM Q.ORG
You can change the order of columns in the report by changing the form. The default order of the columns on the form is the same order in which they are named in the query.
You can select a column more than once; this allows you to use multiple aggregation functions on the form.
You can select up to 750 column names (or expressions) in Db2® for z/OS® and up to 255 when connected to DB2® for VSE and VM databases.
You can use a column name in a WHERE clause without using the column name in the SELECT clause.
- To select only the ID and NAME columns from the Q.STAFF table,
use a query like the following:
SELECT ID, NAME FROM Q.STAFF
- To select the NAME and ID columns from the Q.STAFF table, and
list NAME first, use a query like the following:
SELECT NAME, ID FROM Q.STAFF
Add descriptive columns
You can add a column of descriptive information to your report by putting a quoted constant in the column list of your SELECT statement. The length of a constant is determined by the database. Constants can contain alphabetic characters, numeric characters, or a combination of the two. The following example lists the names and addresses of people in the Q.APPLICANT table with 14 years of education, and identifies each as an applicant.
SELECT NAME, ADDRESS, 'APPLICANT' FROM Q.APPLICANT WHERE EDLEVEL = 14 ORDER BY NAME
Produces this report:
NAME ADDRESS COL1 --------- ----------------- --------- CASALS PALO ALTO,CA APPLICANT REID ENDICOTT,NY APPLICANT RICHOWSKI TUCSON,AZ APPLICANT
The report includes three columns: one containing names, one containing addresses, and a newly created column containing the word APPLICANT for each row selected. The database manager adds a column name to the newly created column. This name varies, depending on the database manager used at your site. You can change this column name using the form panels.
SELECT DEPTNUMBstatement) that produces a list of employees who work in the Eastern division:
SELECT NAME, ID FROM Q.STAFF WHERE DEPT = SOME (SELECT DEPTNUMB FROM Q.ORG WHERE DIVISION='EASTERN')
First, the subquery finds the department numbers in the Eastern division. Then, the main query finds employees who work in any of these departments.
When there are several subqueries, the last one is executed first; the first one is executed last.
Each of the following examples includes one subquery, which is highlighted.
SELECT DEPT, NAME, SALARY FROM Q.STAFF CORRVAR WHERE SALARY = (SELECT MAX(SALARY) FROM Q.STAFF WHERE DEPT = CORRVAR.DEPT)
SELECT ID, NAME FROM Q.STAFF WHERE DEPT IN (SELECT DISTINCT DEPTNUMB FROM Q.ORG WHERE DIVISION = 'MIDWEST') ORDER BY ID
SELECT DEPT, AVG(SALARY) FROM Q.STAFF GROUP BY DEPT HAVING AVG(SALARY) > (SELECT AVG(SALARY) FROM Q.STAFF)
Accessing QMF Data Service (QDS) data
QMF users can use the QMF Data Service feature to access non Db2 data such as VSAM, IMS, sequential files, SMF data, SYSLOG data and more.
To access QDS data, the QMF for TSO and CICS® global variable DSQEC_DS_SUPPORT must be set to a value of '1'; for more information about setting the DSQEC_DS_SUPPORT global variable, see Global variables that control how commands and procedures are executed.
QMF Data Service data sources are accessed through three part table names in the SQL queries, Prompted queries, or Query-by-Example queries. QMF Data Service might join one or more sources that exist at the server. SQL accepted by QMF Data Service is a subset of SQL accepted by Db2 for z/OS. Refer to the QMF Data Service SQL guide for accepted SQL syntax.