Comments (1)
  • Add a Comment
  • Edit
  • More Actions v
  • Quarantine this Entry

1 SASHULL commented Permalink

The condition ":PONUM in (select pl.ponum from poline pl where exists (select * from item where itemnum = pl.itemnum and hazardid is not null) )" is not well designed for either SQL efficiency or accuracy for multi org/multi site environments. <div>&nbsp;</div> For example, if there are 8 sites, each with their own PO #1000, it will evaluate as true for the PO for all sites if one of those 8 POs has a hazardous item. Also, if multi-org and there are multiple item sets, it will evaluate as true if that item number is a hazardous item in any of the sets (not just the one that the current site uses). <div>&nbsp;</div> A better example would be: <br /> EXISTS(SELECT 1 FROM poline WHERE ponum=:PONUM and siteid=:SITEID and exists(SELECT 1 FROM item WHERE itemnum=poline.itemnum and itemsetid=poline.itemsetid and item.hazardid is not null)). <div>&nbsp;</div> Instead of having to pull all POs that have a hazardous item (which can take a very long time if the database has a lot of POLINEs or ITEMS) and then comparing your value against that list this will query hitting the primary index of both POLINE and ITEM to quickly return whether or not a record exists. It also ensures that the condition checks the correct records for relevancy.