37.0    HFQL/SQL Overview
  • HFQL: Direct SQL Access
  • 37.0 HFQL/SQL Overview
  • 37.1 SQL Syntax
  • 37.2 SQL Syntax: Select
  • 37.3 SQL Syntax: Where
  • 37.4 SQL Syntax: Limitations
  • 37.5 SQL Syntax: Examples
    • 37.2    SQL Syntax: Select   
       Table of Contents

    SQL Syntax

    37.1.1HFQL SQL Syntax

     

    The HFQL SQL syntax is designed to feel natural to users who are familiar with standard SQL.

    A simple example is shown below:

    SELECT
    name[0].family as family,
    name[0].given[0] as given, 
    birthDate,
    identifier.where(system='http://hl7.org/fhir/sid/us-ssn').first().value as SSN
FROM
    Patient
WHERE
    active = true
LIMIT 10

    This example selects all resources of type Patient from the repository with an active value of true. This is equivalent to performing the following REST query: http://example.com/fhir/Patient?active=true&_count=10

    This query returns four columns, containing the first family and given name, the birthDate, and the first identifier with a specific Identifier.system value.

    37.1.1.1History

    HFQL is inspired by the Firely Query Language, although it also has significant differences. Our original intention was to implement the same FQL syntax, but once the JDBC driver was created it became apparent during testing that JDBC-compliant tools became very fussy about the differences between FQL and SQL such as:

    • The FROM clause coming before the SELECT clause
    • Repeating elements returning an array or a scalar for a column depending on the underlying data

    In the end, we decided that the intended use cases for HFQL are different enough that a different syntax that was closer to raw SQL was justified.

    37.1.2Syntax Overview

     

    See the following pages to learn more about the HFQL SQL syntax:

       37.0    HFQL/SQL Overview
  • HFQL: Direct SQL Access
  • 37.0 HFQL/SQL Overview
  • 37.1 SQL Syntax
  • 37.2 SQL Syntax: Select
  • 37.3 SQL Syntax: Where
  • 37.4 SQL Syntax: Limitations
  • 37.5 SQL Syntax: Examples
    • 37.2    SQL Syntax: Select