Skip to content

GetCDMValue

aggregating category: cdm-helpers no `||`

Looks up a field's value on another CDM table by an exact (optionally composite) key.

Fetches a field's value from a named CDM table by matching one or more key fields against values from the current row — the general, exact-match companion to GetCDMVisitId (which matches by date interval) and GetCDMTableId (which returns the id via a stored index map).

This is the supported way to pull a value out of another CDM table. The rule records a build-order dependency on the looked-up table at configure time, and at run time it does a getFromCache lookup and returns the requested field — so the looked-up table is fully built first and the value is simply read back. It is a standalone lookup, not a join.

Keys are given as two parallel arrays, the same way the rest of the system takes key/value lists: a list of key fields on the looked-up table and a list of values (field references or literals) from the current row, matched position-by-position. The arrays must be the same length. The order you list the key fields in does not matter — the lookup is matched against the table's index by field, so it resolves the same regardless of declared key order.

If the key matches several rows, each match is a result — so on a non-unique key the rule produces a multi-sized result (use [] / LoopOver / Spawn downstream, exactly like any other multi-result rule). With a uniquely-keyed lookup it is a single value.

A miss is visible, not silent. If no row matches the key (or a key value is NULL), the rule raises a recoverable per-row error naming the key — unless an optional final default value is given, in which case the default is returned on a miss. This mirrors GetCDMTableId.

Parameters

1 — table.return_field Mandatory — accepts: table+field (@table:field)

The CDM table to read from and the field to return, e.g. @procedure_occurrence:procedure_occurrence_id.

2 — keyFields Mandatory — accepts: array of fields (:-notation)

Key fields on the looked-up table, e.g. [:person_id, :procedure_source_value]. Must not repeat a field.

3 — values Mandatory — accepts: array of values (fields or literals)

Values from the current row matched against keyFields position-by-position, e.g. [:person_id, :proc_src].

4 — default Mandatory — accepts: any (optional)

Returned when the key matches no row (or a key value is NULL). Its type must match the return field. Omit it to make a miss a recoverable per-row error instead.

Output type

The output type matches the type of the returned field; one result per matched row.

Requires

  • The looked-up table, the return field, and all key fields must exist.
  • The key-field array and the value array must have the same (non-zero) length, with no repeated key field.
  • A default, if given, must match the return field's type.

Examples

Single-key lookup

GetCDMValue(@person:person_id, [:person_source_value], [:patient_id])

Composite-key lookup

GetCDMValue(@procedure_occurrence:procedure_occurrence_id, [:person_id, :procedure_source_value], [:person_id, :proc_src])

With a default on a miss

GetCDMValue(@person:person_id, [:person_source_value], [:patient_id], 0)

See also

GetCDMVisitId, GetCDMTableId, GetCDMFieldValue