How Corticon is expressed in SQL

SQL Queries provide tremendous power to the access, retrieval, and management of data records. If you have done the ADC and Batch samples, you have used SQL queries and saw how using a different one causes different processing.

Variable Substitution is Corticon's technique for in-memory data to dynamically create SQL statements with the SQL value as a template in the tables CORTICON_ADC_READ_DEFS, CORTICON_ADC_WRITE_DEFS, and CORTICON_BATCH_READ. This is expressed in statements as a value in curly braces, like this: {Patient.patientId}.

For example, the sample batch queries are:

SELECT patientId from Patient
SELECT patientId from Patient WHERE region IN ({Patient.region})        

The first SELECT query selects all patients. The second uses a parameter that will match on a specified region value in a Patient record.

You might want to create a query that uses multiple parameters such as:

SELECT patientId FROM Patient WHERE region IN ({Patient.region}) AND gender IN ({Patient.gender})

that would be specified in the Web Console batch configuration like this:

The READ and WRITE queries allow for multiple statements by exposing values in the CORTICON_ADC_READ and CORTICON_ADC_WRITE tables that link to a corresponding CORTICON_ADC_READ_DEFS and CORTICON_ADC_WRITE_DEFS table for the sequence of steps for the SQL statements.

Here, the sample's Ruleflow property for the Service Call-out's chose the CorticonADC.read service and the Query name IndicatedPatients. That referenced ID=2 in the CORTICON_ADC_READ table: