Basic Select statement in DB2 for i SQL
Basic Select statement in DB2 for i SQL, SQL SELECT, select, dml, data manipulation language
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


  • 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 

    Post a Comment

    © AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain