The evaluate logic helps you to avoid multiple if statements in your program. This also avoids confusion in understanding COBOL logic.
We already know the syntax for evaluate is:
EVALUATE TRUE
WHEN CONDITION
PERFORM PARA1
END-EVALUATE;
Why “when other ” you need to mention in evaluate -the reason is very simple:
To avoid ambiguity to the compiler, you need to mention it.
The other way is , if the conditions are not matched, it helps what action to take.
One more point is, during validations for Numeric or character, you may get scenario like spaces – so to handle spaces what action it should take you need to mention the when other condition.
[ Learning DB2 SQL functions ]In DB2 there are five aggregate functions. The general thumb rule is aggregate functions can be applied on group of column values. The list is COUNT,SUM, AVG, MAX and MIN.These are typical functions being used across all the projects.
The best example for COUNT:
EXEC SQL SELECT COUNT(*) INTO :FEMALE FROM DSN8A10.EMP WHERE SEX = 'F';
Read the specific behaviour of “COUNT function” vs other aggregate functions.
Additional points on COUNT function
When no rows are present in the column COUNT functions returns zero. But all other functions returns NULL.
SELECT DEPT, AVG(SALARY), MAX(SALARY), MIN(SALARY)
INTO :WS-DEPT, :WS-AVG, :WS-MAX, :WS-MIN
FROM TEST.EMPLOYEES;
Two typical points to note on aggregate functions:
If a GROUP BY clause is specified in a query and the intermediate result from the FROM, WHERE, GROUP BY, and HAVING clauses is the empty set, then the aggregate functions are not applied and the result of the query is the empty set.
If the GROUP BY clause is not specified in a query and the intermediate result table of the FROM, WHERE, and HAVING clauses is the empty set, then the aggregate functions are applied to the empty set.
This is not allowed using one aggregate function in another aggregate function
[QUERYID in SYSQUERY Catalog Table]In DB2-COBOL program, you can write simple SQL queries to complex SQL queries that depending on the project. Almost all data intensive projects writing SQL queries is a usual practice.
The data stored in SYIBM.SYSQUERY catalog table helps you find information about each SQL Query. This is the beginning step to validate before you are going to do EXPLAIN_PLAN on PLAN_TABLE (read more info here)
While writing Queries you will add a column name in SQL query. That is called QUERYNO. It says the query number you are using in that project.
Example SQL Query with QUERYNO:
EXEC SQL
SELECT MAX(BONUS), MIN(BONUS), AVG(BONUS)
INTO :MAX, :MIN, :AVG
FROM DSN8A10.EMP
WITH UR
QUERYNO 13
END-EXEC;
After writing Query, and binding, the information about SQL query is written into SYSIBM.SYSQUERY table. This catalog table assigns a row to each SQL Query you have written.
From this table, you will know, about the valid access path is assigned, PLAN name, Package name and Package version etc.
Also, Collection name you can see for this SQL query. The useful information provided in this catalog table is a reference, if you want to tweak/influence the access path that already determined by DB2 as default.
The most important use of referring this table is , to know valid access path for this SQL Query is assigned or not.
