Skip to content

Instantly share code, notes, and snippets.

@balasista

balasista/queries.sql

Last active March 22, 2023 23:34
Show Gist options
  • Save balasista/28cc4734db71a2c97930dfd55c31c631 to your computer and use it in GitHub Desktop.
Save balasista/28cc4734db71a2c97930dfd55c31c631 to your computer and use it in GitHub Desktop.
Download ZIP
bmpn-dmn-queries
Raw
queries.sql
-- Query to retrieve a bpmn for a given input
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di ON di.name = 'input_name' AND di.version = dt.version
WHERE di.input_xml->>'input_variable' = 'input_value';
-- Query to retrieve a bpmn for a multiple inputs
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di1 ON di1.name = 'input_name_1' AND di1.version = dt.version
JOIN decision_input di2 ON di2.name = 'input_name_2' AND di2.version = dt.version
WHERE di1.input_xml->>'input_variable_1' = 'input_value_1'
AND di2.input_xml->>'input_variable_2' = 'input_value_2';
-- Query to retrieve specifc version of BPMN process definition for a given input
SELECT pd.name, pd.version
FROM process_definition pd
JOIN process_decision_table pdt ON pdt.process_definition_id = pd.id
JOIN decision_table dt ON dt.id = pdt.decision_table_id
JOIN decision_input di ON di.name = 'input_name' AND di.version = dt.version
WHERE di.input_xml->>'input_variable' = 'input_value'
AND pd.version = 2;
-- Query to retrieve specifc version of DMN for a given input
SELECT name, version
FROM decision_table
WHERE name = 'decision_table_name'
AND version = 2
AND table_xml->>'input_variable' = 'input_value';
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment