Need Help; Query repeating Rows.

[former_member224264]

Dear Experts,

We have devised this query by using query generator and when executed, the item nos are repeating even though we have used the "Distinct" function. please help us.

SELECT DISTINCT T0.DocEntry,T0.[DocNum], T0.[PostDate], T0.DueDate, T0.[ItemCode], T0.[PlannedQty], T1.[ItemCode], T1.[PlannedQty], T3.OnHand, T0.Status, T1.[IssuedQty], T0.Comments FROM OWOR T0 INNER JOIN WOR1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode inner JOIN OITW T3 ON T1.[ItemCode] = T3.[ItemCode] WHERE T1.[IssuedQty]<=0 and (T0.[Status] ='P' or T0.[Status] ='R') ORDER BY T0.[PostDate]

Can anyone please help us?

Regards.

[former_member610331]

Hi Mekhala P,

It would help if you could explain query output expected after your query is executed. That will help me to review your current query and suggest any other forms of query, if it is needed.

Regards,

Sam

[former_member224264]

Greetings Sam B,

The above query fetches the list of production orders pending for the release of the input material from various stores. This informs the user whether he/she can release and create issue from production for the output part depending on the availability of the input part.

Regards.

[mynynachau]

Your comment will be removed, now, after you had raised an alert. We processed your alert which makes your comment outdated. Therefore, it will be removed. Thanks!

[former_member324899]

Hello Mekhala P,

Problem with OITW, it contains items with all warehouse onhand quantity.

You have to join warehouse code of T1 with WhsCode of OITW T3 like

OITW T3 ON T1.[ItemCode] = T3.[ItemCode] and T1.warehouse=T3.WhsCode

Regards,

Zuber Kazi

[former_member224264]

Greetings Zuber Kazi,

Did try with your suggestion and found that the items are repeating three times in consecutive rows.

Regards.