Microsoft Dynamics GP (Archived)

SQL code help

(0) Share

Report

Posted on by Community Member

Need SQL help - the code below works fine

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

but when I try to add a WHERE stmt

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S
WHERE S.SALESTERR = 'JA'

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S
WHERE S.SALESTERR = 'JA'

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

i get this error message:

Msg 156, Level 15, State 1, Line 19
Incorrect syntax near the keyword 'LEFT'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'LEFT'.

It doesn't make sense - probably something stupid but I don't see it

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (2)

MattPaulen 6,912 on at

Like (0)

Report

Try moving your WHERE statements below your joins:

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

WHERE S.SALESTERR = 'JA'

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

WHERE S.SALESTERR = 'JA'

ORDER BY S.SOPNUMBE

GO

Was this reply helpful? Yes No
Verified answer

MattPaulen 6,912 on at

Like (0)

Report

You'll also want to change the where clause to be SALSTERR instead of SALESTERR

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (0)

Report

NOPE - now I get this message:

Msg 207, Level 16, State 1, Line 21

Invalid column name 'SALESTERR'.

Msg 207, Level 16, State 1, Line 45

Invalid column name 'SALESTERR'.

I've tried it with S.SALESTERR, T.SALESTERR and just SALESTERR

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

ugh - you saw it!!!! I thank you

Was this reply helpful? Yes No
MattPaulen 6,912 on at

Like (0)

Report

Not a problem. Sometimes you just need another set of eyes (I know I do all the time)

Was this reply helpful? Yes No

Community site session details

SQL code help

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S
WHERE S.SALESTERR = 'JA'

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S
WHERE S.SALESTERR = 'JA'

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

i get this error message:

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

Community site session details

SQL code help

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP10100 S WHERE S.SALESTERR = 'JA'

LEFT OUTER JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP30200 S WHERE S.SALESTERR = 'JA'

LEFT JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

i get this error message:

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP10100 S

LEFT OUTER JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

UNION ALL

SELECT S.ORDOCAMT, S.SALSTERR, S.SLPRSNID, S.SOPNUMBE, S.DOCDATE, S.SOPTYPE, S.CUSTNMBR, T.CUSTCLAS, T.CUSTNAME, T.USERDEF1, S.SUBTOTAL, S.DOCAMNT, S.MRKDNAMT

FROM SOP30200 S

LEFT JOIN RM00101 T ON T.CUSTNMBR = S.CUSTNMBR

ORDER BY S.SOPNUMBE

GO

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Featured topics

Product updates

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP10100 S
WHERE S.SALESTERR = 'JA'

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

FROM SOP30200 S
WHERE S.SALESTERR = 'JA'

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

LEFT OUTER JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR

SELECT S.ORDOCAMT,
S.SALSTERR,
S.SLPRSNID,
S.SOPNUMBE,
S.DOCDATE,
S.SOPTYPE,
S.CUSTNMBR,
T.CUSTCLAS,
T.CUSTNAME,
T.USERDEF1,
S.SUBTOTAL,
S.DOCAMNT,
S.MRKDNAMT

LEFT JOIN RM00101 T
ON T.CUSTNMBR = S.CUSTNMBR