DB2 VALUES and SELECT INTO constructs

Where these constructs sit in embedded SQL

Embedded SQL in COBOL, PL/I, C, or Assembler mixes SQL statements with host language logic. The precompiler or SQL coprocessor turns each SQL statement into a call to Db2 and maps host variables into the SQLDA and SQLCA. SELECT INTO and VALUES INTO are assignment-style statements: after a successful execution, named targets in the program contain the selected or computed values. They are not the same as a cursor OPEN / FETCH loop, and they are not the same as INSERT, which writes into a table rather than into program storage.

Choosing the wrong construct is a common design mistake. Teams sometimes open a cursor for a primary-key lookup that can never return more than one row, adding OPEN, FETCH, and CLOSE noise for no benefit. Other teams use SELECT INTO on a department predicate that can return dozens of employees, then spend an afternoon explaining SQLCODE -811. The constructs below exist so you can match the SQL shape to the cardinality you actually need.

The VALUES construct

VALUES builds one or more rows whose columns are expressions. It is a form of fullselect, so it can appear wherever a fullselect is legal: as a nested table in the FROM clause, as a source for INSERT … SELECT, as a branch of UNION, EXCEPT, or INTERSECT, or as the expression list behind VALUES INTO. Each row in a multi-row VALUES list must have the same number of expressions, and corresponding expressions must have compatible types.

1
2
3
4
5
6
7
8
9
10
11
SELECT CODE, LABEL
  FROM (VALUES ('A00', 'SPIFFY COMPUTER SERVICE DIV.'),
               ('B01', 'PLANNING'),
               ('C01', 'INFORMATION CENTER'))
       AS T(CODE, LABEL);
 
SELECT EMPNO, ADJ_SALARY
  FROM DSN8C10.EMP E
  INNER JOIN (VALUES ('000010', 1000),
                      ('000020', 500)) AS A(EMPNO, ADJ)
    ON E.EMPNO = A.EMPNO;

Always name the result columns in the correlation clause, such as AS T(CODE, LABEL). Without names, the engine still produces columns, but they are awkward to reference in outer queries and joins. Keep VALUES lists small and local. When many programs share the same code list, a real reference table is safer than constants copied into SQL statements across packages.

Expressions and scalar functions inside VALUES

A VALUES item is not limited to a literal. It can be a host variable, a special register, an arithmetic expression, a CASE expression, or a scalar function such as CHAR, DECIMAL, COALESCE, SUBSTR, or UPPER. That flexibility is why VALUES is useful both as a tiny inline table and as the engine behind VALUES INTO.

1
2
3
4
5
6
SELECT EMPNO,
       CHAR(CURRENT DATE, ISO) AS RUN_DATE,
       COALESCE(NULLIF(TRIM(WORKDEPT), ''), 'NONE') AS DEPT_KEY
  FROM (VALUES (:HV-EMPNO)) AS K(EMPNO)
  INNER JOIN DSN8C10.EMP E
    ON E.EMPNO = K.EMPNO;

Assignment and casting rules still apply. If a scalar function returns a type that does not match the target host variable or outer column expectation, cast explicitly. String encoding can also matter when you combine special registers with dummy tables or character host variables; be consistent with CCSID expectations for the application package.

VALUES INTO: expressions into host variables

VALUES INTO evaluates one or more expressions and assigns the results to host variables, SQL variables, SQL parameters, or global variables. It does not name a FROM table. Use it when you need today's date, a calculation, a special register, or a scalar function result in program storage without pretending to query a business table.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
EXEC SQL
  VALUES CURRENT DATE,
         CURRENT TIME,
         CURRENT TIMESTAMP,
         CURRENT SQLID
    INTO :HV-DATE,
         :HV-TIME,
         :HV-TS,
         :HV-SQLID
END-EXEC.
 
EXEC SQL
  VALUES DECIMAL(:HV-AMOUNT * :HV-RATE, 11, 2),
         COALESCE(:HV-OVERRIDE, :HV-DEFAULT-CODE)
    INTO :HV-CHARGE,
         :HV-CODE
END-EXEC.

The number of expressions must equal the number of INTO targets, and each target must be assignable from its source expression. Null-capable expressions still need indicator variables in host languages that cannot store SQL nulls directly. Prefer VALUES INTO over SELECT … FROM SYSIBM.SYSDUMMY1 INTO … when the only goal is expression assignment; the intent is clearer and you avoid an unnecessary table reference.

Do not confuse application VALUES INTO with the trigger-action VALUES form that evaluates an expression for side effect and discards the result. In application programs, VALUES INTO is an assignment statement. In a basic trigger, a bare VALUES expression is a different construct used to invoke a function, not to populate host variables.

SELECT INTO: one query row into variables

SELECT INTO produces a result table of at most one row and assigns that row's values to targets. It is the classic embedded-SQL singleton lookup: find the employee, account, or control row identified by a unique key and move the columns into working storage. The select list can mix columns, constants, host variables, expressions, and aggregates, as long as the final result remains one row and each item has a matching target.

1
2
3
4
5
6
7
8
9
10
11
12
EXEC SQL
  SELECT EMPNO,
         LASTNAME,
         SALARY,
         SALARY + :HV-RAISE
    INTO :HV-EMPNO,
         :HV-LASTNAME,
         :HV-SALARY:HV-SALARY-IND,
         :HV-NEW-SAL
    FROM DSN8C10.EMP
   WHERE EMPNO = :HV-EMPNO-IN
END-EXEC.

Host variables in the INTO list must be visible to the precompiler, typically through WORKING-STORAGE declarations or a DCLGEN copybook. Varying-length character targets need the length-and-data layout the precompiler understands. Nullable columns need a companion indicator variable, usually declared as SMALLINT. After a successful SELECT INTO, test the indicator before using the host value; after a failure or +100, do not trust the previous contents of those fields.

SQLCODE +100: no row found

When SELECT INTO finds no qualifying row, Db2 sets SQLCODE +100 and SQLSTATE 02000. The INTO targets are not assigned. This is a not-found condition, not a hard error, unless your WHENEVER or error framework treats every non-zero SQLCODE as fatal. Programs that ignore +100 and continue using host variables are reading stale data from the previous successful call—one of the most common inquiry bugs on the platform.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
           EXEC SQL
                SELECT LASTNAME, SALARY
                  INTO :HV-LASTNAME, :HV-SALARY:HV-SALARY-IND
                  FROM DSN8C10.EMP
                 WHERE EMPNO = :HV-KEY
           END-EXEC.
           EVALUATE SQLCODE
             WHEN 0
               PERFORM PROCESS-EMPLOYEE
             WHEN +100
               PERFORM EMPLOYEE-NOT-FOUND
             WHEN OTHER
               PERFORM SQL-ERROR
           END-EVALUATE.

Aggregates change the not-found story. SELECT COUNT(*) INTO :HV-N returns SQLCODE 0 even when no rows match the WHERE clause; the count is zero. Likewise, AVG over an empty set typically returns a null average with SQLCODE 0, not +100. Know whether your statement is a row lookup or an aggregate before you write the not-found branch.

SQLCODE -811: more than one row

SELECT INTO fails with SQLCODE -811 (SQLSTATE 21000) when more than one row qualifies. Db2 refuses to guess which row you meant. That behavior is deliberate: a singleton statement that silently picks an arbitrary row would hide data and predicate problems. Treat -811 as a signal that the SQL cardinality does not match the program model.

• Tighten the WHERE clause to a unique key if the business rule really is “one row.”
• Use an aggregate if you meant a summary of many rows rather than one detail row.
• Add ORDER BY and FETCH FIRST 1 ROW ONLY only when you intentionally want the first ordered row and can document that rule.
• Switch to a cursor when the application must process each matching row.

1
2
3
4
5
6
7
8
9
10
11
12
-- Dangerous if WORKDEPT can match many employees:
SELECT LASTNAME INTO :HV-LASTNAME
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :HV-DEPT;
 
-- Intentional “latest hire in department”:
SELECT LASTNAME, HIREDATE
  INTO :HV-LASTNAME, :HV-HIREDATE
  FROM DSN8C10.EMP
 WHERE WORKDEPT = :HV-DEPT
 ORDER BY HIREDATE DESC
 FETCH FIRST 1 ROW ONLY;

Single-row validation techniques

Before you ship a SELECT INTO, prove that the statement cannot return two rows under real data conditions. Validation is both a SQL design task and a test task. Unique indexes and primary keys are the strongest guarantees. FETCH FIRST without ORDER BY prevents -811 but does not define a business rule for which row wins, so it is a weak substitute for unique access when correctness matters.

In review checklists, ask three questions: What uniqueness rule makes this one row? What should the program do on +100? What should happen if data ever violates uniqueness and produces -811? Answering those questions in the design note prevents silent wrong-row bugs later.

Contrast with cursors

A cursor is the right tool when zero, one, or many rows may arrive and the program must process them individually. You DECLARE, OPEN, FETCH in a loop while SQLCODE is 0, then CLOSE. Each FETCH can return +100 when the result set is exhausted. That +100 means end of cursor, which is related to but not identical in program flow to a SELECT INTO that never found a row.

SELECT INTO wins when cardinality is known to be zero or one and you need one shot into variables. Cursor wins when cardinality is unknown or greater than one, when you need positioned UPDATE or DELETE, or when you want to hold a result set across several program steps. Do not use SELECT INTO inside a loop that repeatedly requeries the next key if a single cursor or set-oriented statement would do the same work more clearly and cheaply.

1
2
3
4
5
6
7
8
9
DECLARE C1 CURSOR FOR
  SELECT EMPNO, LASTNAME, SALARY
    FROM DSN8C10.EMP
   WHERE WORKDEPT = :HV-DEPT
   ORDER BY EMPNO;
 
OPEN C1;
FETCH C1 INTO :HV-EMPNO, :HV-LASTNAME, :HV-SALARY:HV-SALARY-IND;
-- loop until SQLCODE = +100, then CLOSE C1

Contrast with INSERT … VALUES

INSERT … VALUES uses the VALUES keyword, but its purpose is opposite to SELECT INTO and VALUES INTO. INSERT writes a new row into a table. SELECT INTO and VALUES INTO read or compute values into variables. Confusing the two leads to wrong mental models during code review: an INSERT success does not populate host variables with the inserted columns unless you also use a SELECT, a transition table reference, or an IDENTITY / SEQUENCE retrieval pattern designed for that purpose.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Write a row into a table:
INSERT INTO SESSION.STAGING (CODE, LABEL, EFF_DATE)
  VALUES (:HV-CODE, :HV-LABEL, CURRENT DATE);
 
-- Multi-row VALUES as an INSERT source:
INSERT INTO SESSION.STAGING (CODE, LABEL)
  SELECT CODE, LABEL
    FROM (VALUES ('A00', 'SPIFFY'),
                 ('B01', 'PLANNING')) AS X(CODE, LABEL);
 
-- Read one row into variables (not an INSERT):
SELECT CODE, LABEL
  INTO :HV-CODE, :HV-LABEL
  FROM SESSION.STAGING
 WHERE CODE = :HV-KEY;

Use INSERT … VALUES when creating data. Use VALUES as a nested table when you need a small constant row set as a query or INSERT source. Use VALUES INTO when assigning expressions to variables. Use SELECT INTO when retrieving one existing row. Keeping those four jobs separate makes embedded SQL easier to read and safer to maintain.

Practical embedded-SQL checklist

• Declare host variables and indicators so the precompiler can see them before the SQL statement.
• Match select-list or VALUES expression count to INTO target count, in order.
• Check SQLCODE after every SELECT INTO or VALUES INTO before using the targets.
• Treat +100 as not found for row lookups; remember aggregates often return 0 instead.
• Treat -811 as a cardinality defect and fix uniqueness or switch to a cursor.
• Prefer unique predicates over FETCH FIRST when the business rule is “the one row.”
• Prefer VALUES INTO for special registers and pure expressions; reserve SELECT INTO for table or view retrieval.

These constructs look small, but they sit on the hot path of almost every online inquiry and many batch edit routines. Clear cardinality, honest SQLCODE handling, and the right choice among VALUES, VALUES INTO, SELECT INTO, cursor, and INSERT prevent both abends and quiet wrong answers.

Explain it like I'm 5

Imagine a toy box with labeled bins. SELECT INTO is asking for the one toy with a special sticker and putting it in your backpack. If that sticker is missing, the helper says “not found” (+100) and your backpack stays as it was. If two toys have the same sticker, the helper refuses to guess (-811). VALUES INTO is writing today's date on a sticky note without opening the toy box at all. VALUES as a tiny table is making three pretend toys so you can practice lining them up. A cursor is walking along a whole shelf and picking up every toy one by one. INSERT … VALUES is putting a new toy into the box, not taking one out.

Exercises

1. Write a COBOL-style SELECT INTO that loads LASTNAME and SALARY for EMPNO '000010', including a salary indicator, and list the SQLCODE branches for 0, +100, and -811.
2. Rewrite a SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1 INTO :HV-TS statement as VALUES INTO and explain why the rewrite is clearer.
3. Build a nested VALUES table of three department codes, join it to DSN8C10.DEPT, and describe how you would find codes that do not exist in DEPT.
4. Explain when FETCH FIRST 1 ROW ONLY is acceptable for SELECT INTO and when it hides a uniqueness problem you should fix instead.
5. Compare INSERT INTO T VALUES (:A, :B) with SELECT A, B INTO :A, :B FROM T WHERE KEY = :K. State which statement changes the table and which statement changes host variables.
6. Design a small cursor DECLARE / OPEN / FETCH / CLOSE sketch for all employees in a department, and state why SELECT INTO would be the wrong choice for that workload.