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?