Microsoft Dynamics GP (Archived)

SQL query help

(0) Share

Report

Posted on by painterisaac

3,015

I can't figure out why my SQL code isn't working. I am trying to add three tables. Thank you

SELECT

dbo.RM20101.DOCDATE,

dbo.RM20101.ORTRXAMT,

dbo.RM20101.DOCNUMBR,

dbo.RM00101.CUSTNAME,

dbo.RM00101.CUSTCLAS,

dbo.RM20101.CHEKNMBR,

dbo.RM00101.SLPRSNID,

dbo.RM20101.CSPORNBR,

dbo.RM00301.SLPRSNFN,

dbo.RM00301.SPRSNSLN

FROM

dbo.RM00101 with (nolock)

INNER JOIN

dbo.RM20101 with (nolock) ON dbo.RM00101.CUSTNMBR = dbo.RM20101.CUSTNMBR

dbo.RM00301 with (nolock) ON dbo.RM00301.SLPRSNID = dbo.RM20101.SLPRSNID

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

Mahmoud Saadi 32,738 on at

Like (0)

Report

SELECT A.DOCDATE,
       A.ORTRXAMT,
       A.DOCNUMBR,
       A.CUSTNMBR,
       B.CUSTNAME,
       B.CUSTCLAS,
       A.CHEKNMBR,
       A.CSPORNBR,
       A.SLPRSNID,
       A.SLPRSNID,
       C.SLPRSNFN,
       C.SPRSNSLN
       FROM RM20101 AS A
INNER JOIN RM00101 AS B
ON A.CUSTNMBR = B.CUSTNMBR
LEFT OUTER JOIN RM00301 AS C
ON A.SLPRSNID = C.SLPRSNID

Was this reply helpful? Yes No
painterisaac 3,015 on at

Like (0)

Report

Hmmm...the salesperson first and last name is coming up blank. Any idea why?

Was this reply helpful? Yes No
Mahmoud Saadi 32,738 on at

Like (0)

Report

if there is a salesman Id with blank first and last name, then they are already not filled out at the salesman window. Otherwise, if the salesman id, first and last name are all null, then there is no corresponding salesman for the open document.

Was this reply helpful? Yes No
Verified answer

MG-16101311-0 26,225 on at

Like (0)

Report

There's only one thing wrong with your query (your original question) and that is, you are missing a LEFT OUTER join to your Salesperson Master:

SELECT

RM20101.DOCDATE,

RM20101.ORTRXAMT,

RM20101.DOCNUMBR,

RM00101.CUSTNAME,

RM00101.CUSTCLAS,

RM20101.CHEKNMBR,

RM00101.SLPRSNID,

RM20101.CSPORNBR,

RM00301.SLPRSNFN,

RM00301.SPRSNSLN

FROM

RM00101 with (nolock)

INNER JOIN RM20101 with (nolock) ON RM00101.CUSTNMBR = RM20101.CUSTNMBR

LEFT OUTER JOIN RM00301 with (nolock) ON RM00301.SLPRSNID = RM20101.SLPRSNID

Was this reply helpful? Yes No
Verified answer

painterisaac 3,015 on at

Like (0)

Report

Thank you for help, both of you.

To get the SalesPerson First and Last name you have to change some things on the left outer join so that it refers to the customer master. Pulling from RM20101 will result in a null value.

SELECT

RM20101.DOCDATE,

RM20101.ORTRXAMT,

RM20101.DOCNUMBR,

RM00101.CUSTNAME,

RM00101.CUSTCLAS,

RM20101.CHEKNMBR,

RM00101.SLPRSNID,

RM20101.CSPORNBR,

RM00301.SLPRSNFN,

RM00301.SPRSNSLN

FROM

RM00101 with (nolock)

INNER JOIN RM20101 with (nolock) ON RM00101.CUSTNMBR = RM20101.CUSTNMBR

LEFT OUTER JOIN RM00301 with (nolock) ON RM00101.SLPRSNID = RM00301.SLPRSNID

Was this reply helpful? Yes No

Community site session details

SQL query help

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 query help

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