3 CREATE VIEW artifact_vw
5 SELECT artifact.artifact_id,
6 artifact.group_artifact_id,
9 artifact.artifact_group_id,
10 artifact.resolution_id,
12 artifact.submitted_by,
18 u.user_name AS assigned_unixname,
19 u.realname AS assigned_realname,
20 u.email AS assigned_email,
21 u2.user_name AS submitted_unixname,
22 u2.realname AS submitted_realname,
23 u2.email AS submitted_email,
24 artifact_status.status_name,
25 artifact_category.category_name,
26 artifact_group.group_name,
27 artifact_resolution.resolution_name,
28 coalesce(max(artifact_history.entrydate), artifact.open_date) AS update_date,
29 coalesce(max(artifact_message.adddate), artifact.open_date) AS message_date
37 LEFT JOIN artifact_history
38 on artifact.artifact_id = artifact_history.artifact_id
39 LEFT JOIN artifact_message
40 on artifact.artifact_id = artifact_message.artifact_id
41 WHERE artifact.assigned_to = u.user_id
42 AND artifact.submitted_by = u2.user_id
43 AND artifact.status_id = artifact_status.id
44 AND artifact.category_id = artifact_category.id
45 AND artifact.artifact_group_id = artifact_group.id
46 AND artifact.resolution_id = artifact_resolution.id
49 artifact.group_artifact_id,
52 artifact.artifact_group_id,
53 artifact.resolution_id,
55 artifact.submitted_by,
67 artifact_status.status_name,
68 artifact_category.category_name,
69 artifact_group.group_name,
70 artifact_resolution.resolution_name;