The DataPortal Query module allows querying of data in a Composable DataPortal using Entity SQL. It takes a DataPortal ID, an SQL query, and a list of objects as parameters and returns a Composable Table.
See below for a complete discussion of inputs and options.
The following example DataFlow demonstrates many of the possible ways to use the DataPortal Query module. This DataFlow uses the DataPortal Query sample DataPortal model, which creates a DataPortal called
DataPortalQuery by default. WARNING: this DataFlow deletes all existing data in the DataPortal, so only run it against a test DataPortal. You can change the name in the Excel file if you already have a
DataPortalQuery DataPortal, just be sure to select the right DataPortal when configuring the DataFlow.
Adding New Data
This DataFlow uses the DataPortal Sync module to add entries to the DataPortal we can query from. These will create 5 entries, each having two FamilyHistoryEntries.
Querying Data Using Entity SQL
To ensure the proper types are returned if the query produces no data, it is usually best practice to not use the VALUE keyword. The module will provide a warning if the user attempts to use it and no data is returned. The recommended way of returning all data from a table is as shown below:
There is no '*' selector in Entity SQL. If you want all the rows to be returned in a query you must manually specify them. For a full list of the differences between Transactional SQL queries and Entity SQL queries refer to the official documentation.
The output of the DataPortal Query module is a Composable Table. If the object is a one-to-one relationship such as Double, the value will simply be returned as it exists in the database. If the object is a one-to-many relationship in the case of FamHistoryEntries which is a Table, an error will be thrown. To reference these write a SQL query like the one shown here that joins the tables:
This is the Id for the DataPortal you wish to query. Simply click on it and select the DataPortal from the list that appears.
This is the SQL query you wish to execute on the DataPortal. Specific formatting can be found in the example.
This takes in a list of KeyValuePairs of type
Cases AS c
FamilyHistories AS h
ON c.Id = h.CaseId
WHERE h.HasFamilyHistory = @hasFamilyHistory