By using this website, you agree to our Terms of Use (click here)
Hello,
I have a GI where I need to include certain Sales Orders AND exclude orders in that subset where they have 1 particular NONSTOCK inventory item "COLOFEE"
Hoping someone can help with the funky GI conditions functions:
I can do this in sql using the Having clause:
Select c.OrderNbr
from soorder C, Soline A, Inventoryitem B
where c.OrderNbr=a.OrderNbr
and A.inventoryid=B.inventoryid
and B.inventorycd = 'colofee'
group by C.OrderNbr
having sum(case when b.inventoryCd = 'colofee' then 1 else 0 end) = 0
help is appreciated!
k2
Try Left Joining an additional SOLine and use it to filter your main query.
Join on ordertype, ordernbr and filter where inventorycd=colofee.
Add another filter SOLine2.OrderNbr is empty.
This will only return rows where there's no match against an order that has a row with colofee.
You might have a problem if an order has the COLOFEE item twice, as it will duplicate the rows in the main query.
@roycelithgo ok, i think i followed your comments but i'm sure i missed something, as the query doesn't return any rows now, even when I have a matching SO:
You have the COLOFEE filter on the wrong table (needs to be SOLine2). And you'll also need to join InventoryItem to SOLine2 to apply the filter on InventoryCD. You might need a 2nd join of InventoryItem depending on how you're using it in the Results.
Got it working - give this a go.
Note that the data is returning at line level so if you want it at order level it needs Group criteria (as mine has).