The SQL on FHIR v2 spec defines a %rowIndex environment variable that holds the 0-based index of the current element within the collection being iterated by forEach, forEachOrNull, or repeat. It allows columns to capture position for ordering or surrogate keys.
Spec reference: https://build.fhir.org/ig/FHIR/sql-on-fhir-v2/functional-model.html#the-foreach-and-foreachornull-functions (sections covering the functional model and unionAll interaction with %rowIndex).
Source markdown in the upstream repo: https://github.com/FHIR/sql-on-fhir-v2/blob/master/input/pagecontent/functional-model.md
The corresponding test suite (sqlonfhir/tests/row_index.json) was pulled into this repo by the submodule bump in #9 and currently has nine failing tests against the MSSQL implementation:
%rowIndex at top level%rowIndex for surrogate key%rowIndex in unionAll inside forEach%rowIndex in unionAll without forEach%rowIndex with forEach%rowIndex with forEachOrNull%rowIndex with nested forEach%rowIndex with repeat%rowIndex with unionAll
These are not tagged #experimental, so they show up as red in the test report artifact.
The SQL on FHIR v2 spec defines a
%rowIndexenvironment variable that holds the 0-based index of the current element within the collection being iterated byforEach,forEachOrNull, orrepeat. It allows columns to capture position for ordering or surrogate keys.Spec reference: https://build.fhir.org/ig/FHIR/sql-on-fhir-v2/functional-model.html#the-foreach-and-foreachornull-functions (sections covering the functional model and
unionAllinteraction with%rowIndex).Source markdown in the upstream repo: https://github.com/FHIR/sql-on-fhir-v2/blob/master/input/pagecontent/functional-model.md
The corresponding test suite (
sqlonfhir/tests/row_index.json) was pulled into this repo by the submodule bump in #9 and currently has nine failing tests against the MSSQL implementation:%rowIndex at top level%rowIndex for surrogate key%rowIndex in unionAll inside forEach%rowIndex in unionAll without forEach%rowIndex with forEach%rowIndex with forEachOrNull%rowIndex with nested forEach%rowIndex with repeat%rowIndex with unionAllThese are not tagged
#experimental, so they show up as red in the test report artifact.