Van workflow

Dat zijn nog eens queries:

SELECT	tblWorkFlowEntry.wfe_ID AS task_ID,
	tblNodes.node_modified AS date,
	'WFE' AS task_type,
	tblWorkFlowEntry.wfe_description AS task_description,
	tblWorkFlow.ws_user_ID AS task_from_user_ID,
	tblNodes.node_ID AS task_node_ID,
	'' AS task_data

FROM	tblNodes
	LEFT JOIN tblWorkFlowEntry
	  ON tblWorkFlowEntry.wfe_ID = tblNodes.node_wfe_ID
	LEFT JOIN tblWorkFlow
	  ON tblWorkFlow.workflow_ID = tblWorkFlowEntry.wfe_workflow_ID
	
WHERE	tblWorkFlowEntry.wfe_userGroup_ID IN (#groups#)
  AND	tblNodes.node_wfe_complete=FALSE

UNION

SELECT	tblWorkFlowValidation.wfv_ID AS task_ID,
	tblNodes.node_modified AS date,
	'WFV' AS task_type,
	tblWorkFlowValidation.wfv_description AS task_description,
	tblWorkFlow.ws_user_ID AS task_from_user_ID,
	tblNodes.node_ID AS task_node_ID,
	'' AS task_data

FROM	tblNodes
	LEFT JOIN tblWorkFlowEntry
	  ON tblWorkFlowEntry.wfe_ID = tblNodes.node_wfe_ID
	LEFT JOIN tblWorkFlow
	  ON tblWorkFlow.workflow_ID = tblWorkFlowEntry.wfe_workflow_ID
	INNER JOIN tblWorkFlowValidation
	  ON tblWorkFlowValidation.wfv_wfe_ID = tblWorkFlowEntry.wfe_ID
	LEFT JOIN tblUserGroup_WorkFlowValidation
	  ON tblUserGroup_WorkFlowValidation.uwfv_wfv_ID = 
                     tblWorkFlowValidation.wfv_ID

WHERE	tblUserGroup_WorkFlowValidation.uwfv_userGroup_ID IN (#groups#)
  AND	tblNodes.node_wfe_complete=TRUE
  AND	NOT EXISTS (
		SELECT	*
		FROM	tblNodes_WorkFlowValidation
		WHERE	nwfv_node_ID=tblNodes.node_ID
		  AND	nwfv_wfv_ID=tblWorkFlowValidation.wfv_ID
	)

UNION

SELECT	task_ID,
	task_type,
	task_date AS date,
	task_description,
	task_from_user_ID,
	task_node_ID,
	task_data
FROM tblTasks
WHERE task_userGroup_ID IN (#groups#)

ORDER BY date

Go Stijn!

3 reacties op “Van workflow”

  1. Verdekke, query from hell. Allé, tzal er wel aan liggen dat hij zo groot is, beetje onoverzichtelijk en ik weet natuurlijk ook niet wat de bedoeling is. Had 13/20 voor SQL in’t 1ste jaar, en dit jaar databasetechnologieën aan’t volgen. Nu nog voze theorie over restrictions en triggers en zever, maar binnenkort PL/SQL.
    Trouwens, welk dialect is dit? Wij krijgen ORACLE, en kvind da proper.

Reacties zijn gesloten.