SQL on FHIR
3.0.0-ballot - STU 3 Ballot International flag

SQL on FHIR, published by HL7 International / FHIR Infrastructure. This guide is not an authorized publication; it is the continuous build for version 3.0.0-ballot built by the FHIR (HL7® FHIR® Standard) CI Build. This version is based on the current content of https://github.com/HL7/sql-on-fhir/ and changes regularly. See the Directory of published versions

: Conditions Translated to ICD-10

Page standards status: Informative

Raw json | Download

{
  "resourceType" : "Library",
  "id" : "ConditionsToIcd10Query",
  "meta" : {
    "profile" : [
      🔗 "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/SQLQuery"
    ]
  },
  "language" : "en",
  "text" : {
    "status" : "extensions",
    "div" : "<div xmlns=\"http://www.w3.org/1999/xhtml\">\n  <table class=\"narrative-table\">\n    <tbody>\n<tr>\n\n\n<th colspan=\"2\" scope=\"row\" class=\"row-header\"><a name=\"metadata\"> </a>Metadata</th>\n\n\n</tr>\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Title</th>\n\n\n\n<td class=\"content-container\">Conditions Translated to ICD-10</td>\n\n\n</tr>\n\n\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Version</th>\n\n\n\n<td class=\"content-container\">3.0.0-ballot</td>\n\n\n</tr>\n\n\n\n\n\n\n\n  \n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Identifier</th>\n\n\n\n<td class=\"content-container\">urn:oid:2.16.840.1.113883.4.642.40.77.28.8</td>\n\n\n</tr>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Jurisdiction</th>\n\n\n\n<td class=\"content-container\">\n    World\n\n\n</td>\n\n\n</tr>\n\n\n\n\n\n\n\n\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Steward (Publisher)</th>\n\n\n\n<td class=\"content-container\">HL7 International / FHIR Infrastructure</td>\n\n\n</tr>\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Steward Contact</th>\n\n\n\n<td class=\"content-container\">HL7 International / FHIR Infrastructure</td>\n\n\n</tr>\n\n\n\n\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Description</th>\n\n\n\n<td class=\"content-container\"><div><p>Returns each condition with the ICD-10 code its SNOMED CT code maps to under\nthe <code>http://example.org/ConceptMap/sct-to-icd10|2026</code> concept map. The map is\ndeclared as a dependency and the runner exposes it under the <code>sct_to_icd10</code>\nlabel as a relation with the columns <code>source_system</code>, <code>source_version</code>,\n<code>source_code</code>, <code>source_display</code>, <code>target_system</code>, <code>target_version</code>,\n<code>target_code</code>, <code>target_display</code> and <code>relationship</code> (see\n<a href=\"terminology-in-sql.html#concept-map-relation\">Terminology in SQL</a>). The\n<code>LEFT JOIN</code> keeps conditions the map does not translate, with a null\n<code>target_code</code>; the <code>relationship</code> predicate excludes rows asserting that a\ntarget is not related to the source, and admits null so that a <code>noMap</code> row is\nkept.</p>\n<pre><code class=\"language-sql\">SELECT conditions.patient_id,\n       conditions.code,\n       sct_to_icd10.target_code,\n       sct_to_icd10.relationship\nFROM conditions\nLEFT JOIN sct_to_icd10\n  ON sct_to_icd10.source_system = conditions.system\n AND sct_to_icd10.source_code = conditions.code\n AND (sct_to_icd10.relationship IS NULL\n      OR sct_to_icd10.relationship &lt;&gt; 'not-related-to')\n</code></pre>\n</div></td>\n\n\n</tr>\n\n\n\n\n\n\n\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Type</th>\n\n\n\n<td class=\"content-container\">\n    <a href=\"CodeSystem-LibraryTypesCodes.html\">http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes</a>#sql-query\n\n\n</td>\n\n\n</tr>\n\n\n\n\n\n\n\n\n\n\n\n\n\n<tr>\n\n\n<th colspan=\"2\" scope=\"row\" class=\"row-header\"><a name=\"parameters\"> </a>Parameters</th>\n\n\n</tr>\n\n\n<tr>\n\n\n<th scope=\"row\" class=\"row-header\">Parameter</th>\n\n\n\n<td class=\"content-container\">None</td>\n\n\n</tr>\n\n\n\n\n\n\n\n\n  <tr>\n\n\n<th colspan=\"2\" scope=\"row\" class=\"row-header\">Library Content</th>\n\n\n</tr>\n  \n    \n    <tr>\n      <th scope=\"row\" class=\"row-header\">application/sqlContent</th>\n      <td>\n        <pre style=\"border: none;\" class=\"content-container highlight language-cql\"><code>Encoded data </code></pre>\n      </td>\n    </tr>\n    \n  \n\n\n<tr>\n\n\n<th colspan=\"2\" scope=\"row\" class=\"row-header\"><a name=\"related-artifacts\"> </a>Citations and References</th>\n\n\n</tr>\n\n\n<tr>\n  \n  \n  \n  \n  \n\n<th scope=\"row\" class=\"row-header\">Dependency</th>\n\n\n  \n  \n  <td class=\"content-container\">\n    \n    <em>Description</em>: Condition view with patient_id, system, version and code columns\n    \n    <br/>\n    \n    \n    \n    \n    \n    \n    <em>Resource</em>: <code>https://example.org/ViewDefinition/conditions</code>\n    <br/>\n    <em>Canonical URL</em>: <tt>https://example.org/ViewDefinition/conditions</tt>\n    \n    \n    \n  </td>\n</tr>\n\n<tr>\n  \n  \n  \n  \n  \n\n<th scope=\"row\" class=\"row-header\">Dependency</th>\n\n\n  \n  \n  <td class=\"content-container\">\n    \n    <em>Description</em>: SNOMED CT to ICD-10 concept map, exposed as a relation\n    \n    <br/>\n    \n    \n    \n    \n    \n    \n    <em>Resource</em>: <code>http://example.org/ConceptMap/sct-to-icd10|2026</code>\n    <br/>\n    <em>Canonical URL</em>: <tt>http://example.org/ConceptMap/sct-to-icd10|2026</tt>\n    \n    \n    \n  </td>\n</tr>\n\n\n\n\n<tr>\n  <th colspan=\"2\" scope=\"row\" class=\"row-header\">Generated using version 0.5.6 of the sample-content-ig Liquid templates</th>\n</tr>\n\n    </tbody>\n  </table>\n</div>"
  },
  "url" : "http://hl7.org/fhir/uv/sql-on-fhir/Library/ConditionsToIcd10Query",
  "identifier" : [
    {
      "system" : "urn:ietf:rfc:3986",
      "value" : "urn:oid:2.16.840.1.113883.4.642.40.77.28.8"
    }
  ],
  "version" : "3.0.0-ballot",
  "name" : "ConditionsToIcd10Query",
  "title" : "Conditions Translated to ICD-10",
  "status" : "active",
  "type" : {
    "coding" : [
      {
        "system" : "http://hl7.org/fhir/uv/sql-on-fhir/CodeSystem/LibraryTypesCodes",
        "code" : "sql-query"
      }
    ]
  },
  "date" : "2026-10-13T03:59:03+00:00",
  "publisher" : "HL7 International / FHIR Infrastructure",
  "contact" : [
    {
      "name" : "HL7 International / FHIR Infrastructure",
      "telecom" : [
        {
          "system" : "url",
          "value" : "http://www.hl7.org/Special/committees/fiwg"
        }
      ]
    }
  ],
  "description" : "Returns each condition with the ICD-10 code its SNOMED CT code maps to under\nthe `http://example.org/ConceptMap/sct-to-icd10|2026` concept map. The map is\ndeclared as a dependency and the runner exposes it under the `sct_to_icd10`\nlabel as a relation with the columns `source_system`, `source_version`,\n`source_code`, `source_display`, `target_system`, `target_version`,\n`target_code`, `target_display` and `relationship` (see\n[Terminology in SQL](terminology-in-sql.html#concept-map-relation)). The\n`LEFT JOIN` keeps conditions the map does not translate, with a null\n`target_code`; the `relationship` predicate excludes rows asserting that a\ntarget is not related to the source, and admits null so that a `noMap` row is\nkept.\n\n```sql\nSELECT conditions.patient_id,\n       conditions.code,\n       sct_to_icd10.target_code,\n       sct_to_icd10.relationship\nFROM conditions\nLEFT JOIN sct_to_icd10\n  ON sct_to_icd10.source_system = conditions.system\n AND sct_to_icd10.source_code = conditions.code\n AND (sct_to_icd10.relationship IS NULL\n      OR sct_to_icd10.relationship <> 'not-related-to')\n```",
  "jurisdiction" : [
    {
      "coding" : [
        {
          "system" : "http://unstats.un.org/unsd/methods/m49/m49.htm",
          "code" : "001",
          "display" : "World"
        }
      ]
    }
  ],
  "relatedArtifact" : [
    {
      "type" : "depends-on",
      "label" : "conditions",
      "display" : "Condition view with patient_id, system, version and code columns",
      "resource" : "https://example.org/ViewDefinition/conditions"
    },
    {
      "type" : "depends-on",
      "label" : "sct_to_icd10",
      "display" : "SNOMED CT to ICD-10 concept map, exposed as a relation",
      "resource" : "http://example.org/ConceptMap/sct-to-icd10|2026"
    }
  ],
  "content" : [
    {
      "extension" : [
        {
          "url" : "http://hl7.org/fhir/uv/sql-on-fhir/StructureDefinition/sql-text",
          "valueString" : "SELECT conditions.patient_id,\n       conditions.code,\n       sct_to_icd10.target_code,\n       sct_to_icd10.relationship\nFROM conditions\nLEFT JOIN sct_to_icd10\n  ON sct_to_icd10.source_system = conditions.system\n AND sct_to_icd10.source_code = conditions.code\n AND (sct_to_icd10.relationship IS NULL\n      OR sct_to_icd10.relationship <> 'not-related-to')"
        }
      ],
      "contentType" : "application/sql",
      "data" : "U0VMRUNUIGNvbmRpdGlvbnMucGF0aWVudF9pZCwKICAgICAgIGNvbmRpdGlvbnMuY29kZSwKICAgICAgIHNjdF90b19pY2QxMC50YXJnZXRfY29kZSwKICAgICAgIHNjdF90b19pY2QxMC5yZWxhdGlvbnNoaXAKRlJPTSBjb25kaXRpb25zCkxFRlQgSk9JTiBzY3RfdG9faWNkMTAKICBPTiBzY3RfdG9faWNkMTAuc291cmNlX3N5c3RlbSA9IGNvbmRpdGlvbnMuc3lzdGVtCiBBTkQgc2N0X3RvX2ljZDEwLnNvdXJjZV9jb2RlID0gY29uZGl0aW9ucy5jb2RlCiBBTkQgKHNjdF90b19pY2QxMC5yZWxhdGlvbnNoaXAgSVMgTlVMTAogICAgICBPUiBzY3RfdG9faWNkMTAucmVsYXRpb25zaGlwIDw+ICdub3QtcmVsYXRlZC10bycp"
    }
  ]
}