A Lobby Data Source's Condition SQL uses `... AND LOCATION_NO NOT IN ('CODE1', 'CODE2', 'CODE3')` to exclude specific inventory locations, but instead of excluding just those locations, the query unexpectedly returns no rows at all.

Q: A Lobby Data Source's Condition SQL uses `... AND LOCATION_NO NOT IN ('CODE1', 'CODE2', 'CODE3')` to exclude specific inventory locations, but instead of excluding just those locations, the query unexpectedly returns no rows at all.

This is a classic SQL NULL-handling trap with NOT IN — if the compared column (LOCATION_NO in this case) is NULL on any row being evaluated, Oracle's NOT IN comparison against that row evaluates to unknown rather than true, which silently excludes that entire row from the result, and if enough of your data has a NULL in that column it can end up excluding everything. Add an explicit NULL-handling clause alongside the NOT IN check, wrapped together in parentheses: AND (LOCATION_NO NOT IN ('CODE1', 'CODE2', 'CODE3') OR LOCATION_NO IS NULL) — this correctly excludes only the specifically named locations while still including rows where the location column itself is NULL.

Answer

This is a classic SQL NULL-handling trap with NOT IN — if the compared column (LOCATION_NO in this case) is NULL on any row being evaluated, Oracle's NOT IN comparison against that row evaluates to unknown rather than true, which silently excludes that entire row from the result, and if enough of your data has a NULL in that column it can end up excluding everything. Add an explicit NULL-handling clause alongside the NOT IN check, wrapped together in parentheses: AND (LOCATION_NO NOT IN ('CODE1', 'CODE2', 'CODE3') OR LOCATION_NO IS NULL) — this correctly excludes only the specifically named locations while still including rows where the location column itself is NULL.

← Browse Platform, Aurena, and customization questions