Easytrieve SELECT Statement

Four SELECT Contexts at a Glance

SELECT Statement (Sort Selection)

SORT normally copies every input record to the sorted output file in key order. When you need only a subset—or want to modify records before they enter the sort—you code SORT ... BEFORE proc-name. Easytrieve supplies input records to that procedure one at a time. For each record you want in the output file, you must execute SELECT. Omit SELECT and the record is dropped from sort output even though the procedure ran. The sort utility still receives only selected records when the sort phase executes.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
FILE PERSNL FB(150 1800)
%PERSNL
FILE SORTWRK FB(150 1800) VIRTUAL
COPY PERSNL
 
SORT PERSNL TO SORTWRK USING +
    (REGION, BRANCH, DEPT, NAME-LAST, NAME-FIRST) +
    NAME MYSORT BEFORE SCREENER
 
SCREENER. PROC
   IF MARITAL-STAT = 'S' AND SEX = 1
      SELECT
   END-IF
END-PROC

SCREENER keeps only single female employees in the sorted work file. Major-to-minor key order in USING controls break hierarchy: REGION major, BRANCH minor, DEPT more minor, then name fields. D suffix on a key field requests descending order for that key only. Variable-length fields and certain index types cannot be sort keys—verify DEFINE attributes before coding USING.

Sort SELECT Switch Semantics

SELECT only sets a switch; it does not immediately write the record. If logic SELECTs the same record twice in one procedure invocation, the record still appears once in sorted output. If you SELECT and later issue STOP in the same procedure pass, the record is not selected—STOP cancels the pending inclusion. This interacts with STOP used to cap sort volume: a procedure that STOPs when RECORD-COUNT exceeds fifty ends prescreening and runs the sort; any record mid-procedure where STOP follows SELECT loses that selection.

Valid Fields in SORT Procedures

Broadcom limits field references in sort procedures to fields of the input file (plus working storage you use for tests). You cannot reference sort output file fields that do not exist yet. GET and PUT are among I/O statements invalid in SORT-invoked procedures—use SELECT, MOVE, IF, and DISPLAY instead. Keep BEFORE procedures fast; prescreening millions of rows with complex IF trees may push teams toward DFSORT INCLUDE/OMIT instead—compare with your easytrieve-vs-dfsort guidance when performance tuning.

SELECT Statement (Report Selection)

REPORT-INPUT is a special-name report procedure performed for each PRINT statement when coded. To include the current report input in further report processing, execute SELECT inside REPORT-INPUT. Unselected input bypasses continued processing for that PRINT cycle. If you omit REPORT-INPUT entirely, PRINT-driven records flow into the report under default rules without an explicit SELECT per record.

1
2
3
4
5
6
REPORT-INPUT. PROC
    IF ZIP NE HOLD-ZIP
       HOLD-ZIP = ZIP
       SELECT
    END-IF
END-PROC

This pattern prints detail only when ZIP changes—classic control-break filtering before LINE formatting. HOLD-ZIP working storage remembers the prior value. Duplicate SELECT on the same logical input still produces one printed occurrence, matching sort selection switch behavior.

SELECT Versus PRINT

PRINT names a report and triggers input retrieval for that report. REPORT-INPUT SELECT decides whether the retrieved row participates in totals and LINE output for that pass. Teams sometimes duplicate logic in JOB body IF and REPORT-INPUT—consolidate on one layer to avoid skew where JOB prints diagnostics but report suppresses lines, or vice versa.

SELECT Statement (Non-file SQL)

JOB INPUT SQL declares automatic SQL retrieval without naming a Library SQL FILE. Exactly one non-file SQL SELECT may appear per JOB activity, and it must be the first statement in the JOB body immediately following the JOB header. The SELECT lists columns, FROM tables, optional WHERE, GROUP BY, HAVING, UNION, ORDER BY, and mandatory INTO :host-variable list for fetched columns.

1
2
3
4
5
6
7
8
9
JOB INPUT SQL NAME SQL-JOB
  SELECT EMPNAME, SALARY
    FROM EMP
   WHERE DEPT = 100
    INTO :EMP-NAME :EMP-SAL
  IF SQLCODE NE 0
     DISPLAY 'SQL FETCH ISSUE'
  END-IF
  PRINT DEPT100-RPT

Easytrieve generates DECLARE CURSOR, OPEN, FETCH loop, and CLOSE around your JOB logic. SQLCODE 100 signals end of data—FINISH procedure runs, reports wrap, JOB ends. Other SQLCODE values produce diagnostics and terminate execution. On SQL/DS, CONNECT may be generated using PARM USERID credentials. The cursor closes after the JOB activity completes.

File-Based SQL SELECT

When you define an SQL FILE in Library, automatic input or START procedure may issue SELECT to position the cursor. FILE DEFER delays open until first I/O—use DEFER when START procedure must run SELECT before default cursor open to avoid double-open overhead documented in OPEN statement guidance. File-based SELECT grammar parallels non-file SELECT but ties to the FILE name on JOB INPUT or explicit cursor management in procedures.

Common SELECT Mistakes

• BEFORE sort procedure without SELECT on records you intended to keep.
• SELECT after STOP in same procedure pass—record dropped.
• Two non-file SQL SELECT statements in one JOB—compile error.
• SQL SELECT not first statement after JOB INPUT SQL.
• Expecting SQL SELECT keyword to filter sort input—it does not; use SORT BEFORE.
• REPORT-INPUT never SELECT—unexpected blank reports.

Testing SELECT Logic

1. Count input vs sort output when BEFORE proc filters—reconcile with business rules.
2. Exercise STOP before and after SELECT in sort proc—confirm inclusion rules.
3. REPORT with ZIP change sample—verify only break rows print.
4. SQL JOB with empty WHERE result—confirm FINISH and clean EOJ.
5. Regression: remove REPORT-INPUT—observe default PRINT behavior difference.

Explain It Like I'm Five

Sometimes SELECT is a tiny sticker you put on a paper saying yes, this one goes in the box. At sort time, only papers with stickers get sorted into the new pile. At report time, only stickered papers get printed in the story. That sticker is just the word SELECT on its own line. Other times SELECT is a big sentence asking the database for rows—that is a different game with the same word on the jersey. Learn which game you are playing before you code.

Exercises

1. Write SORT BEFORE proc that SELECTs only REGION = 10 records.
2. Add REPORT-INPUT that SELECTs when AMOUNT GT 1000.
3. Explain duplicate SELECT on one record in sort prescreening.
4. Sketch JOB INPUT SQL with SELECT INTO two host variables.
5. List three differences between sort SELECT and SQL SELECT.