🗃 DB2

DB2 SELECT Statement

Beginner 🕑 20 min read 👁 8 views

SELECT Statement Basics

The SELECT statement retrieves data from DB2 tables. It's the most commonly used SQL statement.

Basic SELECT Syntax

SELECT column1, column2, ...
FROM table_name
WHERE condition
ORDER BY column;

SELECT Clauses

SELECT - Columns to retrieve
FROM - Tables to query
WHERE - Filter conditions
GROUP BY - Group rows
HAVING - Filter groups
ORDER BY - Sort results

Comparison Operators

Operator	Meaning
=	Equal
<>	Not equal
>	Greater than
<	Less than
>=	Greater or equal
<=	Less or equal
BETWEEN	Range check
LIKE	Pattern match
IN	Value list
IS NULL	Null check

Aggregate Functions

COUNT(*) - Count rows
SUM(column) - Sum values
AVG(column) - Average value
MAX(column) - Maximum value
MIN(column) - Minimum value

Singleton vs Cursor

Singleton SELECT - Returns one row (INTO clause)
Cursor - Returns multiple rows

Code Example

       * SINGLETON SELECT
           EXEC SQL
               SELECT EMP_NAME, DEPT, SALARY
               INTO :WS-EMP-NAME,
                    :WS-DEPT,
                    :WS-SALARY
               FROM EMPLOYEE
               WHERE EMP_ID = :WS-EMP-ID
           END-EXEC.

       * SELECT WITH MULTIPLE CONDITIONS
           EXEC SQL
               SELECT COUNT(*)
               INTO :WS-COUNT
               FROM EMPLOYEE
               WHERE DEPT = :WS-DEPT
                 AND SALARY > 50000
                 AND HIRE_DATE BETWEEN '2020-01-01'
                                   AND '2023-12-31'
           END-EXEC.

       * SELECT WITH LIKE
           EXEC SQL
               SELECT EMP_NAME
               INTO :WS-EMP-NAME
               FROM EMPLOYEE
               WHERE EMP_NAME LIKE 'JOHN%'
           END-EXEC.

       * SELECT WITH IN
           EXEC SQL
               SELECT SUM(SALARY)
               INTO :WS-TOTAL-SALARY
               FROM EMPLOYEE
               WHERE DEPT IN ('IT', 'HR', 'FIN')
           END-EXEC.

       * SELECT WITH GROUP BY
           EXEC SQL
               SELECT DEPT, AVG(SALARY)
               INTO :WS-DEPT, :WS-AVG-SALARY
               FROM EMPLOYEE
               GROUP BY DEPT
               HAVING COUNT(*) > 5
               ORDER BY DEPT
           END-EXEC.

       * SELECT WITH NULL HANDLING
           EXEC SQL
               SELECT EMP_NAME, MANAGER_ID
               INTO :WS-EMP-NAME,
                    :WS-MGR-ID :WS-MGR-NULL-IND
               FROM EMPLOYEE
               WHERE EMP_ID = :WS-EMP-ID
           END-EXEC.

           IF WS-MGR-NULL-IND < 0
               DISPLAY "NO MANAGER ASSIGNED"
           END-IF.