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

[pressfit]

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?

[ManishPant]

Hi,

Will this work?

SELECT T0."DocNum",T1."BpStateCod", T1."BPStatGSTN",T2."Name"
FROM "ORDR"  T0 
INNER JOIN RDR12 T1 ON T0."DocEntry" = T1."DocEntry" 
INNER JOIN OCST T2 ON T2."Code" = T1."BpStateCod" AND T2."GSTCode"=T1."BPStatGSTN"

Thanks.

Manish

[kothandaraman_nagarajan]

Hi,

Try this,

SELECT T0.DocEntry, L1.NameFROM ORDR T0
INNERJOIN RDR12 T1 ON T0.DocEntry = T1.DocEntry
LEFT JOIN OCST S1 ON T1.LocStatCod = S1.Code

[pressfit]

This is specifically the problem. This query gives wrong results and we need to join the Country field of OCST to get correct results.