Page Comparison

...

The key to this entire design is that there is always a one-to-one translation for anything in the both the provide defining SQL and runtime queries.

New Features

The implementation plan is to divide the work into two phases:

• Phase One - Add support for CTE, CASE Statement, JSON columns/functions to the runtime query services. At the end of this phase a user should be able to run this type of query against any Synapse table/view:

  Code Block

  WITH AGG AS ( SELECT FILE_ID, FILE_NAME, FILE_TYPE, FILE_SIZE, COUNT(PART_ID) AS PART_COUNT, JSON_OBJECT('one', SUM(CASE STAGE WHEN 'one' THEN 1 ELSE 0 END), 'two', SUM(CASE STAGE WHEN 'two' THEN 1 ELSE 0 END)) as PART_STAGE, JSON_OBJECT('min', MIN(AGE), 'max', MAX(AGE)) as PART_AGE, GROUP_CONCAT(DISTINCT PART_ID) AS PART_IDS FROM MATERIAL WHERE FILE_ID IS NOT NULL GROUP BY FILE_ID, FILE_NAME, FILE_TYPE, FILE_SIZE ) SELECT * FROM AGG;

• Phase Two: Add AggregateViews plus the new aggregate facet types as syntactic sugar that will be expanded to the full SQL from phase one at runtime.

To see the full list of features needed to make this design work see the epic:

...