SQL SELECT || DB2 for i SQL

Basic Select statement in DB2 for i SQL

We can use SELECT statement to retrieve data from the table/tables or view/views. We can use SELECT to retrieve data in any way. IF SQL SELECT does not find any row for the search condition an SQLCODE of +100 is returned. IF SQL SELECT finds errors during its execution then a negative SQLCODE is returned. Also, if SQL finds more host variables than results then +326 SQLCODE is returned. This SELECT statement can be used in a cursor or prepared dynamically.

Format of the basic SELECT statement

Name of each column that we want to include in the result

Name of the table/view/PF/LF that contains the data

Search criteria to identify the rows that we want to display

Name of each column used to group the data

Search criteria to uniquely identifies a group

Order of the results

Offset into the result set to enable skipping a number of rows.

Number of rows to return

A Select statement can be written as below

 SELECT column names
     FROM table or view name
     WHERE search condition
     GROUP BY column names
     HAVING search condition
     ORDER BY column-name
     OFFSET number of rows
     FETCH FIRST n ROWS ONLY

Note:

The SELECT and FROM clauses must be specified.

The other clauses are optional.

Character strings specified in an SQL statement WHERE or VALUES clauses are case-sensitive.

SELECT * from PF1 WHERE EMPID='e1'

does not return a result

SELECT * from PF1 WHERE EMPID='E1'

returns a valid employee id

Select the name of columns to retrieve from table PF1

Select empid, empname, managerid from pf1

We can specify minimum one or max 8000 columns to retrieve in SQL SELECT clause, the value will be retrieved in the order in which columns are specified in the SELECT statement. The result will contain all the rows from the table.

Select the all the columns from table PF1

Select * from pf1

The column values will be retrieved in the same order as they are defined in table. For this purpose we use *(asterisk) instead of column names. FROM clause specifies the name of the table that you want to select data from. The result will contain all the rows from the table.

The SELECT list can also contain expressions, including constants, special registers, and scalar full selects. An AS clause can be used to give the resulting column a name.

SELECT empid, empname, managerid, 0 AS status
       FROM pf1
       WHERE empid = 'E1'

The output would be

EMPID       EMPNAME               MANAGERID          STATUS 
E1          EMPNAME1              M1                      0