Statement Nesting

Whenever a parent statement returns some number of rows in a result set, any child statements of the parent statement are executed for every row returned. If a parent statement returns zero rows, the child statements are executed once. This allows other tables to be referenced for further details related to the basic information in the first table.

These child statements can also have child statements, which will be executed for every row returned by its direct parent statement, allowing any number of related tables to be queried. All these child statements can contain parameters in them, in the form @column_name, where column_name must be the name of one of the columns returned by the parent statement. Then, before the statement is executed for each row, the value of the column for that row is used in place of the parameter.

For example, say a child statement had the form:

 SELECT firstname, secondname FROM patientDetails WHERE patientID = @patientID

And the parent statement had returned a result set including:

PatientID
---------
12345
54321
62943

the child statement would be executed three times, once for each row returned, the first time with @patientID replaced by 12345, the second time replaced with 54321 and the third time replaced with 62943.

Statement Iteration

Messages frequently utilize repeating components in order to provide additional information and present one-to-many relationships. For example, in HL7 the PID.PatientAddress field is repeating, as a patient may have multiple addresses. While in some cases only the first repeat may be of interest, in other cases all repeats need to be processed.

Statements (and stored procedure calls) within Rhapsody can be configured to iterate over all repeats of a particular message path. Once iteration is configured, the statement will be executed once for each repeat of the selected message component. The statement can then process each repeat individually.

Example: Inserting Multiple Discharge Events

Consider the following XML message:

<DischargeEvent>
 <Diagnosis>
  <diagnosis_code value="code"/>
  <diagnosis_description value="diagnosis"/>
  <diagnosis_codingSystem value="system1"/>
  <diagnosis_isPrimary value="Y"/>
 </Diagnosis>
 <Diagnosis>
  <diagnosis_code value="code"/>
  <diagnosis_description value="diagnosis2"/>
  <diagnosis_codingSystem value="system2"/>
  <diagnosis_isPrimary value="N"/>
 </Diagnosis>
 <Diagnosis>
  <diagnosis_code value="code"/>
  <diagnosis_description value="diagnosis3"/>
  <diagnosis_codingSystem value="system3"/>
  <diagnosis_isPrimary value="N"/>
 </Diagnosis>
</DischargeEvent>

The DischargeEvent/Diagnosis element repeats 3 times. A statement can be configured to iterate over the DischargeEvent.Diagnosis field. The following SQL could then be used to insert each diagnosis into a separate row:

INSERT INTO Diagnosis (code, description, codingSystem, isPrimary)
VALUES (@DischargeEvent.Diagnosis.diagnosis_code.value,
        @DischargeEvent.Diagnosis.description.value,
        @DischargeEvent.Diagnosis.diagnosis_codingSystem.value,
        @DischargeEvent.Diagnosis.diagnosis_isPrimary.value)

Accessing Message Paths Within Repeating Statements

When specifying message field paths within a repeating statement, care must be taken not to specify a particular repeating index, or the statement may not execute as expected. For example, with the XML message above, if only the first repeat was wanted, we reference field paths like @DischargeEvent.Diagnosis[0].diagnosis_code.value. This field path explicitly states that the first repeat (indexed as zero) of the Diagnosis element should be used.

However, if the statement is repeating, the repeat index should not be specified (@DischargeEvent.Diagnosis.diagnosis_code.value). This is because Rhapsody knows that it is currently in a statement iterating over DischargeEvent.Diagnosis, and so uses the appropriate repeat index during the iteration.

Nested Repeating Statements

Iterating statements can be nested inside each other. When this is done, all repeats of the inner statement are executed once for each repeat of the parent statement. For example, in the HL7 ADTA01 message, the NK1 (next of kin) segment is repeating. In addition, the Address field within the NK1 segment repeats (as each next of kin may have multiple addresses). A statement can be configured to iterate over the NK1 segment, and then a child statement can configured to iterate over NK1.Address.

This has the effect of executing the child statement once for each address of each next of kin.