Subset of SELECT Statement

As indicated in the INSERT statement syntax diagram, not all clauses and options of the SELECT statement are available for you to use in a query within an INSERT statement. The following SELECT clauses and options are not supported in an INSERT statement:
  • FIRST and LIMIT
  • INTO TEMP, INTO RAW, and INTO STANDARD result table options
  • UNION, UNION ALL, INTERSECT, MINUS, and EXCEPT set operators.

In an ANSI-compliant database, if this statement has a WHERE clause that does not return rows, sqlca returns SQLNOTFOUND (100).

If an INSERT statement that is part of a multistatement prepared object inserts no rows, sqlca returns SQLNOTFOUND (100) for both ANSI-compliant databases and databases that are not ANSI-compliant. In databases that are not ANSI-compliant, sqlca returns zero (0) if no rows satisfy the WHERE clause.

In HCL OneDB™, if you are inserting values into a supertable in a table hierarchy, the subquery can reference a subtable. If you are inserting values into a subtable in a table hierarchy, the subquery can reference the supertable if it references only the supertable. That is, the subquery must use the SELECT…FROM ONLY (supertable) syntax.