SQL DML SELECT

What is a SELECT Statement?

The SELECT statement is one of the most fundamental SQL statements, used to retrieve data from one or more tables. The SELECT statement can also be embedded in COBOL programs for use within mainframe applications.

The SELECT statement allows us to retrieve data from one or more tables in DB2 based on specific conditions. This data can be used for viewing, reporting, or further processing within the application. SELECT statements can be simple, retrieving all columns, or complex, using filtering, sorting, joins, and aggregation to obtain precisely the needed data.

Basic Components:

• Columns: Specify which columns to retrieve.
• Filters (WHERE clause): Define conditions to limit results.
• Joins: Combine data from multiple tables.
• Sorting (ORDER BY clause): Arrange results in a specific order.
• Grouping (GROUP BY clause): Organize data into groups, often used with aggregation functions.

Syntax - The basic syntax for a SELECT statement in DB2 is as follows:

SELECT column1, column2, ...
  FROM table_name
 WHERE condition
 GROUP BY column
HAVING condition
 ORDER BY column ASC | DESC;

• column1, column2, ...: Columns to retrieve from the table. Use * to select all columns.
• table_name: The table or tables from which data is retrieved.
• WHERE: Specifies conditions to filter the rows returned.
• GROUP BY: Groups rows that have the same values in specified columns.
• HAVING: Filters groups based on a specified condition.
• ORDER BY: Specifies the sort order of the result set (ascending or descending).

Examples - Basic SELECT Statement

Retrieve all employees in the SALES department and order them by their salary in descending order.

SELECT EMP_ID, EMP_NAME, SALARY
FROM EMPLOYEE
WHERE DEPARTMENT = 'SALES'
ORDER BY SALARY DESC;

• Selects EMP_ID, EMP_NAME, and SALARY columns.
• Filters employees in the SALES department.
• Orders results by SALARY in descending order.

Using SELECT Statements in a COBOL Program

In a COBOL + DB2 program, SELECT statements are embedded within the code using EXEC SQL ... END-EXEC syntax. The retrieved data can then be stored in host variables, which are COBOL variables that hold data fetched by the SELECT statement.

Examples - Embedding a SELECT Statement in COBOL

EXEC SQL
	SELECT EMP_ID, EMP_NAME, SALARY
	  INTO :EMP-ID, :EMP-NAME, :SALARY
	  FROM EMPLOYEE
	 WHERE DEPARTMENT = 'SALES'
	 ORDER BY SALARY DESC
END-EXEC.

• EXEC SQL begins the SQL command, and END-EXEC closes it.
• INTO clause: Maps data to host variables :EMP-ID, :EMP-NAME, and :SALARY.
• Host variables, identified by a colon (:), are COBOL variables that will hold the data from the SELECT statement.

Steps to Code a SELECT Statement in a COBOL Program

1. Declare host variables compatible with DB2 columns or include the host variables (DCLGEN) or declare the host variables in WORKIGN-STORAGE SECTION. Syntax -
   

   EXEC SQL
   	INCLUDE [Host-variable DCLGEN]
   END-EXEC.

2. Fill the data to the host-variables which are going to use in WHERE condition of SELECT.
3. Write the Embedded SQL SELECT Statement: Use EXEC SQL ... END-EXEC to retrieve data into host variables.
4. If any retrieving column has NULL values expected, then provide NULL indicator along with Host-variable.
5. Execute the SELECT query to retrieve the data. Syntax -
   

   EXEC SQL
   	SELECT Column1, column2,…, column-n
   	INTO    Host-variable1, Host-variable2,…, Host variable-N
   	WHERE Condition1 [logical operator] condition2.. Etc,.
   END-EXEC.

6. Check for SQL Errors: After each SQL statement, check SQLCODE in the SQLCA (SQL Communication Area) to handle any errors. Syntax -
   

   IF SQLCODE NOT = 0
   	DISPLAY 'Error in SELECT statement'
   END-IF.

Error Handling for the SELECT Statement in COBOL Program

DB2 uses SQLCODE and SQLSTATE to indicate the outcome of SQL statements. The SQL Communication Area (SQLCA) is automatically populated with details after each SQL execution.

Common SQLCODE Values for SELECT:

• 0: Successful execution.
• 100: No data found (useful in SELECT statements to detect the end of data).
• Negative Values: Indicate various errors (e.g., -803 for duplicate key error).

Examples - Error Handling in a COBOL Program

...
EXEC SQL
	SELECT EMP_ID, EMP_NAME, SALARY
	INTO :EMP-ID, :EMP-NAME, :SALARY
	FROM EMPLOYEE
	WHERE DEPARTMENT = 'SALES'
END-EXEC.

IF SQLCODE = 100
	DISPLAY 'No matching data found'
ELSE 
	IF SQLCODE < 0
		DISPLAY 'SQL Error: ' SQLCODE
	END-IF
END-IF.
...

• SQLCODE = 100: Indicates no matching data was found.
• SQLCODE < 0: Catches other SQL errors. STOP RUN terminates the program for severe errors.

---