Posted by Roy Harvey on 08/01/07 22:46

UNION allows combining the results of two or more SELECT commands that
return equivalent result sets.

When using UNION the SELECT list of all the SELECTs must match up.
That is, they must have the exact same number of columns, and for each
column all must have compatible data types. Your first SELECT list
has 9 items, while the second has only 6. And they don't seem to
"line up", as the fourth item of one is a date, while the fourth item
of the other is a cost.

Roy Harvey
Beacon Falls, CT

On Wed, 01 Aug 2007 13:38:10 -0700, "Joanne M."
<joanne.e.mahoney@gmail.com> wrote:

>As you can probably tell from my query I am not a guru, just trying to
>"gitter done". This query is for Dynamics GP. It is a select with left
>outer and a union. Can someone help debug my syntax? Please? I'll
>create a view out of it and use it in a crystal report.
>
>SELECT openheader.custnmbr, openheader.custname,
>Openheader.sopNUMBe,
>openheader.DOCDATE, openheader.soptype, opendetail.itemnmbr,
>opendetail.xtndprce,
>openheader.frtamnt, opendetail.extdcost
>FROM sop10100 as openheader
>left outer join sop10200 as opendetail on sop10200.sopnumbe =
>sop10100.sopnumbe and sop10200.soptype = sop10100.soptype
>UNION
>SELECT histheader.sopnumbe, histheader.soptype,
>histdetail.itemnmbr,
>histdetail.extdcost,histheader.frtamnt,histdetail.xtndprce
>FROM sop30200 as histheader left outer join sop30300 as
>histdetail on sop30300.sopnumbe = sop30200.sopnumbe
>and sop30300.soptype = sop30200.soptype
>where openheader.custnmbr = 'AARONFIT0001' or histheader.custnmbr =
>'AARONFIT0001'
>
>Joanne M
>Jacksonville, FL

[Back to original message]