SELECT *
FROM def_decision_tree
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
SELECT *
FROM def_decision_treenodes
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
ORDER BY COALESCE(Rank, 100), Guid
SELECT DISTINCT condition_field
FROM def_decision_treenodes
WHERE DecisionName = '{decision_name}'
AND condition_field IS NOT NULL
AND condition_field != ''
CREATE TABLE IF NOT EXISTS {table_name} (
GUID VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
{columns},
Output_Node VARCHAR(50),
Output_Description VARCHAR(255),
Expected_Outcome VARCHAR(50),
Package VARCHAR(50)
)
CREATE TABLE {base_table} (
Guid VARCHAR(255) PRIMARY KEY,
SetGuid VARCHAR(255),
payload JSON,
Expected_Outcome VARCHAR(50),
Verification CHAR(1)
)
INSERT INTO {base_table}
(Guid, SetGuid, payload, Expected_Outcome, Verification)
VALUES ('{rec_guid}', '{set_guid}', '{escaped_json}',
'{expected_outcome}', '{verification}')
INSERT INTO {base_table}
(Guid, SetGuid, payload)
VALUES ('{rec_guid}', '{set_guid}', '{escaped_json}')
SELECT * FROM {input_table}
SELECT * FROM {input_table}
LIMIT {limit}
SELECT * FROM {input_table}
WHERE {where_clause}
SELECT * FROM {input_table}
WHERE {where_clause}
LIMIT {limit}
SELECT payload
FROM {output_table}
UPDATE def_decision_tree
SET operationsql = '{sql_statement}'
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
UPDATE def_decision_tree
SET diagram = '{diagram}'
WHERE package = '{package}'
AND DecisionName = '{decision_name}'
SELECT
JSON_UNQUOTE(JSON_EXTRACT(payload, {outcome_json_path})) AS outcome,
COUNT(*) AS count
FROM {base_table}
GROUP BY outcome
ORDER BY count DESC
DELETE FROM def_decision_treenodes
WHERE DecisionName = '{decision_name}'
AND Package = '{package}'
DELETE FROM def_decision_tree
WHERE DecisionName = '{decision_name}'
AND Package = '{package}'
INSERT INTO def_decision_tree
(Guid, DecisionName, Package, Version)
VALUES ('{guid}', '{decision_name}', '{package}', {version})
SELECT
Verification,
COUNT(*) as count
FROM {output_table}
GROUP BY Verification
SELECT
Guid,
payload,
Expected_Outcome,
Verification
FROM {output_table}
WHERE Verification = 'N'
ORDER BY Guid
LIMIT {limit}
SELECT
Expected_Outcome,
COUNT(*) as count
FROM {output_table}
WHERE Verification = 'N'
GROUP BY Expected_Outcome
ORDER BY count DESC
SELECT
SetGuid,
COUNT(*) as total_records,
SUM(CASE WHEN Verification = 'Y' THEN 1 ELSE 0 END) as passed,
SUM(CASE WHEN Verification = 'N' THEN 1 ELSE 0 END) as failed
FROM {output_table}
GROUP BY SetGuid
DELETE FROM {output_table}
WHERE SetGuid = '{set_guid}'
DELETE FROM {output_table}