Skip to Content
0
May 13, 2020 at 08:50 AM

How can I join a Marketing Document (ORDR) to a Location State (OCST) correctly?

65 Views Last edit May 13, 2020 at 08:51 AM 3 rev

We are facing a problem in joining the ORDR and OCST table correctly. We want a query to give the result of DocEntry and the Name of the State of the Location:

SELECT T0.DocEntry, L1.Name
FROM ORDR T0
INNER JOIN RDR12 T1 ON T0.DocEntry = T1.DocEntry
INNER JOIN OCST S1 ON T1.LocStatCod = S1.Code

This is resulting in incorrect results because the OCST table has states of USA also filled in.
For e.g. if one of the state codes in India is MP, there is a state code MP in USA as well. So, the second join results in 2 rows.

We are missing the Country field i.e.

INNER JOIN OCST S1 ON T1.LocStatCode = S1.Code AND S1.Country = X

What would be the right X here?

I do not want to hard code the country/localization of the company here. Is there a field in ORDR or RDR12 or some other related table in the document which would give us the country of the location?