Обсуждение: 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 PM
>
> Change 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