I would like to join RM00101 (RM Customer MSTR) and RM00102 (Customer Master Address File) into one to get the output in a single report. I tried using query as under :
SELECT CUSTNMBR from RM00101 join RM00102 on CUSTNMBR.RM00101 = CUSTNMBR.RM00102 where INACTIVE '0'.
But the above-said didn't fetch any data rather threw the error.
Msg 4104, Level 16, State 1, Line 1The multi-part identifier "CUSTNMBR.RM00101" could not be bound.Msg 4104, Level 16, State 1, Line 1The multi-part identifier "CUSTNMBR.RM00102" could not be bound.Msg 209, Level 16, State 1, Line 1Ambiguous column name 'CUSTNMBR'.
Purpose for the query is that I am trying to pull whole Customer Master Data which Active having single as well as more than one addresses.
Apperciate any help on this in advance!.
Please try the following when able:
From RM00101 as A
LEFT OUTER JOIN RM00102 AS B ON A.CUSTNMBR = B.CUSTNMBR
Where A.INACTIVE = '0'
Please let me know the results when able.
Try this statement. Replace [TWO] with your database name.
select * from [TWO]..RM00102 T1 with (nolock) INNER JOIN [TWO]..RM00101 T2 with (nolock) on T2.[CUSTNMBR] = T1.[CUSTNMBR] and T2.[ADRSCODE] = T1.[ADRSCODE]
same statement with inactive restriction.
select * from [TWO]..RM00102 T1 with (nolock) INNER JOIN [TWO]..RM00101 T2 with (nolock) on T2.[CUSTNMBR] = T1.[CUSTNMBR] and T2.[ADRSCODE] = T1.[ADRSCODE]where (T2.[INACTIVE] = '0')
Hi Gilbertnm Much Appreciated for helping the newbie ! It worked.
One more question is that something I need to consider same for Supplier Master PM00200 and Suppler Address Table PM0300.. And what if the address is duplicated how to avoid the same. Thanks a lot for the reply!
Glad I could help!!
Yes, the same SQL script logic can be used for the vendor / supplier master data.
Duplication identification / exclusion logic can be tricky depending on the situation. Are you primarily focusing on duplicate street address data (i.e. line 1, line 2, line 3). If yes, you could use the count function on vendor ID where street address (which ever lines you are populating) contain certain text. Once executed, you would focus on those vendor IDs with a count value greater than one. Further investigation would then be required to ID it as a True duplicate or false positive.
Please let me know if you'd like further assistance with this.