`orders`.`discontinued` options upgrade

Facing some serious data inconsistencies with the upgraded order entry API, while replacing the old discontinued column of orders where un-reviewed code led to massive use of SQL to fetch data from the Database rather than using the OpenMRS API. The old SQL to fetch all non discontinued orders may have looked like (in 1.6.x);

SELECT * FROM `orders` WHERE discontinued = '0' AND (ISNULL(start_date) OR start_date >= NOW()) AND (ISNULL(discontinued_date) OR discontinued_date < NOW())

(which would be translated in 1.11.x as)

SELECT * FROM `orders` WHERE (ISNULL(IFNULL(date_activated, scheduled_date)) OR IFNULL(date_activated, scheduled_date) >= NOW()) AND (ISNULL(IFNULL(date_stopped, auto_expire_date)) OR IFNULL(date_stopped, auto_expire_date) < NOW())

Am still correcting these SQL statements, has any one gone SQL while working with either pre or post Order Entry API upgrade.

CC @darius or @burke

Use COALESCE (ANSI SQL) instead of ISNULL / IFNULL.

1 Like

makes no difference @lluismf