SELECT ORDER_NUMBER , '1-NEW' STATUS FROM XNC_ORDER_LINE_STATUS_V GROUP BY ORDER_NUMBER HAVING COUNT(*) = 1 AND MAX(STATUS) = '1-NEW' UNION SELECT ORDER_NUMBER , '5-CLOSED' STATUS FROM XNC_ORDER_LINE_STATUS_V GROUP BY ORDER_NUMBER HAVING COUNT(*) = 1 AND MAX(STATUS) = '5-CLOSED' UNION SELECT ORDER_NUMBER , '6-ERROR' STATUS FROM XNC_ORDER_LINE_STATUS_V WHERE STATUS = '6-ERROR' UNION (SELECT ORDER_NUMBER , '2-OPEN' STATUS FROM XNC_ORDER_LINE_STATUS_V MINUS (SELECT ORDER_NUMBER , '2-OPEN' STATUS FROM XNC_ORDER_LINE_STATUS_V GROUP BY ORDER_NUMBER HAVING COUNT(*) = 1 AND MAX(STATUS) = '1-NEW' UNION SELECT ORDER_NUMBER , '2-OPEN' STATUS FROM XNC_ORDER_LINE_STATUS_V GROUP BY ORDER_NUMBER HAVING COUNT(*) = 1 AND MAX(STATUS) = '5-CLOSED' UNION SELECT ORDER_NUMBER , '2-OPEN' STATUS FROM XNC_ORDER_LINE_STATUS_V WHERE STATUS = '6-ERROR'))