help with sql select

[Joanne M.]

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

[Roy Harvey]

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."
<jo**************@gmail.comwrote:

>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