UNION question

From: Brandon Metcalf <brandon(at)geronimoalloys(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: UNION question
Date: 2009-07-10 16:16:10
Message-ID: Pine.LNX.4.58L.0907101109280.6574@cedar.geronimoalloys.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Hartman, Matthew 2009-07-10 16:23:42 Re: UNION question
Previous Message Tom Lane 2009-07-10 16:14:13 Re: Checkpoint Tuning Question