View source for Using SQL subselects to derive a primary key value

Older versions of OS/400 lack an automatically maintained '''identity column''', to uniquely refer to a given record in a table. Depending on use case, it might be programmatically easier to pick a certain record by a a single value, instead of e.&thinsp;g. a multi-column key.

The most easy way to '''fake''' this is to define a column as numeric and derive a new value at insert time.

<syntaxhighlight lang="sql">
INSERT INTO mytable (id, field1, field2, field3)
SELECT MAX(id)+1,  'foo', 'bar', 'baz' FROM mytable
</syntaxhighlight>

If there is a certain chance that another process is concurrently active, it will derive the same ID and those <tt>INSERT</tt>s will fail with a duplicate key error. As long as this ID is not referenced by other tables as a primary key value, the easiest way to counter this is to programmatically try to insert the record in a loop<ref>You should ensure that only a duplicate key error forces another iteration. Other errors can happen.</ref> until the <tt>INSERT</tt> eventually succeeds. Note that using ''commitment control'' adds another layer of possible pitfalls here.

Similar ways can be done with classic positional API calls, and a LF defined only for the ID column.<ref>An example can be found in the [https://github.com/PoC-dev/as400-sfltemplates/blob/main/v_lodallpg.rpgle v_lodallpg.rpgle] I wrote. Look for subroutine <code>INCLASTID</code>.</ref>

Other ways of obtaining IDs are pointed out briefly in ''Sequence Objects and Identity Columns''.

== See also ==
* [[Using Data Areas in RPG to derive a primary key value]]

== Weblinks ==
* [https://www.datavail.com/blog/sequence-objects-identity-columns/ Sequence Objects and Identity Columns], datavail.com
* [https://github.com/PoC-dev/as400-sfltemplates Subfile Templates], GitHub

== Footnotes ==
<references />

[[Category: SQL]]