SELECT

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

To retrieve all the columns from a table, use an asterisk (*) instead of naming the columns. The format of a SELECT statement used for this selection is:

SELECT * FROM tablename

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 Q.ORG

This query returns all the columns but only displays rows where the department number is 10:

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.

The following statement produces a report with the department names on the left and the department numbers on the right:

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.

Examples:

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.

This query:

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.

Using subqueries

Subqueries select data from a table. The data is then used to test a condition in the WHERE clause of the main query. For example, this query has a subquery (beginning with the SELECT DEPTNUMB statement) 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.

Examples

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.