I have been struggling with Batch reporting for a while now. The problem I am having is when joining OIBT and IBT1, I get a doubling of data on my reports.
OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode AND T0.BatchNum = T1.BatchNum
The entire query looks like this:
SELECT T1.[ItemCode], T1.[BatchNum], T1.[BsDocEntry] FROM OIBT T0 INNER JOIN IBT1 T1 ON T0.ItemCode = T1.ItemCode AND T0.BatchNum = T1.BatchNum WHERE T0.[BatchNum] = 'AA1234' ORDER BY T1.[BatchNum], T1.[BsDocEntry]
Item Code Batch Doc Key
1000 AA1234 29
1000 AA1234 29
1000 AA1234 33
1000 AA1234 33
...
When I do the same report from the IBT1 table without the join, I get
Item Code Batch Doc Key
1000 AA1234 29
1000 AA1234 33
...
I believe the problem is in my join. Any SQL experts have any suggestions?
Thanks
Mike