UNION question
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
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Brandon Metcalf
Sent: Friday, July 10, 2009 12:16 PM
Change it to this:
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
WHERE p.part_id=379
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 t.machine_type_id=1
GROUP BY t.name,j.workorder
ORDER BY avgtime
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital.now.
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
owner@postgresql.org] On Behalf Of Brandon Metcalf
Sent: Friday, July 10, 2009 12:16 PMChange it to this:
Sorry, I forgot that you need to split the GROUP BY clause as well in a
similar manner to the WHERE clause. And unless you have duplicate rows
to eliminate, use UNION ALL rather than UNION for a speed increase.
Matthew Hartman
Programmer/Analyst
Information Management, ICP
Kingston General Hospital
.now.
Brandon Metcalf <brandon@geronimoalloys.com> writes:
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 think that's probably what you want to do. What you're missing is
you need parentheses to put an ORDER BY into an arm of a UNION:
(SELECT ... ORDER BY ...) UNION SELECT ...
Otherwise it wants to consider the ORDER BY as applying to the UNION
output.
regards, tom lane
M == Matthew.Hartman@krcc.on.ca writes:
M> > > From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
M> > > owner@postgresql.org] On Behalf Of Brandon Metcalf
M> > > Sent: Friday, July 10, 2009 12:16 PM
M> >
M> > Change it to this:
M> Sorry, I forgot that you need to split the GROUP BY clause as well in a
M> similar manner to the WHERE clause. And unless you have duplicate rows
M> to eliminate, use UNION ALL rather than UNION for a speed increase.
Thanks. Got it to work.
--
Brandon
t == tgl@sss.pgh.pa.us writes:
t> Brandon Metcalf <brandon@geronimoalloys.com> writes:
t> > I tried moving the last group of WHERE, GROUP BY, and ORDER BY before
t> > the UNION with the query it belongs to, but that results in a
t> > different syntax error.
t> I think that's probably what you want to do. What you're missing is
t> you need parentheses to put an ORDER BY into an arm of a UNION:
t> (SELECT ... ORDER BY ...) UNION SELECT ...
t> Otherwise it wants to consider the ORDER BY as applying to the UNION
t> output.
Indeed. It was the fact that I was trying to move the ORDER BY along
with WHERE and GROUP BY that was giving me grief.
Thanks.
--
Brandon