Is the following even possible? I keep getting a syntax error at the
last WHERE:
ERROR: syntax error at or near "WHERE"
LINE 20: WHERE p.part_id=379 AND t.machine_type_id=1
The SQL is
SELECT t.name AS machine_type_name,
j.workorder,
round(sum(EXTRACT(epoch FROM(j.clockout-
j.clockin))/3600/w.quantity_made)::numeric,2)
AS avgtime
NULL AS employees
FROM jobclock j
JOIN employee e ON e.employee_id=j.employee_id
JOIN machine m ON m.machine_id=j.machine_id
JOIN machine_type t ON t.machine_type_id=m.machine_type_id
JOIN workorder w ON w.workorder=j.workorder
JOIN part p ON p.part_id=w.part_id
UNION
SELECT t.name AS machine_type_name,
NULL AS workorder,
h.time AS avgtime,
employees
FROM part_time_historical h
JOIN machine_type t ON t.machine_type_id=h.machine_type_id
WHERE h.part_id=379 AND h.machine_type_id=1
WHERE p.part_id=379 AND t.machine_type_id=1
GROUP BY t.name,j.workorder
ORDER BY avgtime
I tried moving the last group of WHERE, GROUP BY, and ORDER BY before
the UNION with the query it belongs to, but that results in a
different syntax error.
I'm basically looking to concatenate these two results.
--
Brandon
