DML SELECT


Select statement is the main query hence it is used to retrieve the data from the tables. Select statement also used in the Cursor declaration for retrieving the data for the cursor. Select statement is more powerful hence it has the below clauses which is mainly used in the SELECT to retrieve the data in smarter ways.

FROM
WHERE
GROUP BY
HAVING
ORDER BY

FROM -


FROM Clause is used to provide the table name or multiple tables from where to retrieve the data. FROM clause may have one table or more than one table along with it.

WHERE -


WHERE clause is used to specify the condition to retrieve data from the table/tables. WHERE clause will have the below operators which will be used during the retrieval of the data.

WHERE Operators -


Relational Operator -

 >	-	Greater than
<	-	Less than
>=	-	Greater than or equal to
<= 	- 	Less than or equal to
=	-	Equal to
<>	-	Not equal to

NOT Operator -

NOT operator can be used to validate the negative conditions.

NOT =

BETWEEN Operator -

BETWEEN operator is used to validate the data whether it is within range provided or not. The Range in between the values provided and the values provided in condition are also included. Between operator is used on Numeric columns.

BETWEEN (value1, value2,…, value-n)

Example -


EMPNO BETWEEN (10,20).

In the above example, the select statement will retrieve the rows that the EMPNO columns will have the values 10 to 20 including 10 and 20.

IN operator -

By using the IN operator, SELECT will retrieve the rows which columns will have the values provided in IN operator. IN operator is used on Numeric columns.

IN (value1, value2,…, value-n)

Example -


EMPNO IN(10,20)

In the above example, the select statement will retrieve the rows that the EMPNO columns will have the 10 or 20.

LIKE Operator -

LIKE operator is used on the non Numeric columns. By using like operator, SELECT statement will retrieve the rows which will have the text provided in LIKE.

% is used to concatenate it along with the single char or more than one char to get the rows which will contain the characters provided in Query regardless of the position.

LIKE ‘%characters%’

Example -


  • EMPNAME LIKE ‘%RAO’ - Returns the rows which will contain the RAO at end of the EMPNAME column.
  • EMPNAME LIKE ‘%VASU%’ - Returns the rows which will contain the VASU at the middle of the EMPNAME column.
  • EMPNAME LIKE ‘SRI%’ - Returns the rows which will start with SRI of the EMPNAME column.

EXIST Operator -

EXIST operator used to verify whether at least one row returned by the sub query or not. This is very useful to validate the specific row existed or not. If the sub query returns the value then only main query returns the result. In other cases, it will not return result.

WHERE EXISTS (Sub Query)

GROUP BY -


GROUP BY clause is used to group the rows. Grouping can be done with similar values or based on certain condition.

The columns functions should not apply to the columns provided in GROUP BY.The column used in GROUP BY should exist in the SELECT columns list. Otherwise, the user will get BIND error.

GROUP BY clause can be used on more than one column. If the Grouping column is non nullable column, then all rows which have the null values will become a group.

Syntax -


GROUP BY column1, column2,…etc,.	

HAVING -


HAVING is used to specify the condition for the GROUP BY clause.HAVING is optional eventhough GROUP BY existed. But GROUP BY is mandatory to use HAVING. More than one condition can be used in HAVING.

Syntax -


HAVING contion1 <logical-operator> condition2.. etc,.

ORDER BY -


ORDER BY clause is used to sort the data which is retrived based on the data of specified columns. One or more columns can be used in ORDER BY clause. The columns specified in ORDER BY should be existed in SELECT. Sorting can be done in two ways

  1. Ascending (ASC): Ascending order which is default
  2. Descending (DESC): Descending order

Syntax -


ORDER BY column1 DESC. Column2 ASC,.. etc,.

How SELECT statement can be used in Program:


SELECT statement can be used in two ways inside the program.

  1. SELECT in cursor: Already discussed in previous chapters.
  2. Direct SELECT query:

    • SELECT is used to fetch the single row by using the runtime data in the program.
    • If no aggregate functions are used, SELECT retrieves the data from the single row at a time.
    • SELECT statement is a static statement should be associated with application program.
    • If aggregate functions used, then SELECT will retrieve the data from more than one row based on the aggregate function.
    • To execute the SELECT query through application program, the following accessed required for the user.
      1. If the program is batch program (COBOL + DB2), then the table read access is required for the executing user.
      2. If the program is online (COBOL + DB2 + CICS), the RCT entries should be defined for the program and read access also required for the user executing the program.
        Otherwise, the program execution will ended with -911 or -922.

Syntax -


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

In the above, the order of Host variables should be the order of the columns in SELECT statement.

Steps to code SELECT query in COBOL program:


  1. Include the host variables copybook or declare the host variables in working storage section.

    Syntax -


    EXEC SQL
    	INCLUDE [Host-variable copybook]
    END-EXEC.
    
  2. Fill the data to the HOST-variables which are going to use in WHERE condition of SELECT.
  3. If any retrieving column has NULL values expected, then provide NULL indicator along with Host-variable.
  4. 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.
    

Error handling:


If SELECT query able to get matching row in table for the WHERE condition specified, then SELECT query returns +000 (SUCCESSFUL) as SQLCODE.

If SELECT query not able to get matching rows in table for the WHERE condition specified, then SELECT query returns +100 (ROW NOT FOUND) as SQLCODE.

If SELECT query returning NULL values and NULL indicators not specified in INTO clause, then SELECT query abends with -305 (NULL value exception) as SQLCODE.

If the NULL indicator codes the validation will be like below.

If NULL indicator value	 = -1 (NULL existed in the retrieved column)
			 =  0 (Retrieved column has the proper value)
			 =  2 (Truncated value retrieved)

If SELECT query returning more than one row during the execution, then SELECT query abends with -811 (Multiple rows found) as SQLCODE. If the user has no access to table and trying to perform SELECT query on the table , then SELECT query abends with -922 (Authorization failure) as SQLCODE.