469,890 Members | 1,741 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,890 developers. It's quick & easy.

help with sql select

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

Aug 1 '07 #1
1 1435
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
Aug 1 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by netpurpose | last post: by
28 posts views Thread by stu_gots | last post: by
5 posts views Thread by Craig Keightley | last post: by
23 posts views Thread by casper christensen | last post: by
reply views Thread by Salome Sato | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.