On Tue, Oct 01, 2002 at 10:41:17PM -0700, mgeddert wrote:
> create or replace view events_orders_states asselect o.*,o_states.order_state from events_orders o, (
SELECTorder_id, CASE ( floor (avg ( CASE reg_state WHEN 'canceled'
THEN0 WHEN 'waiting' THEN 1 WHEN 'pending' THEN 2
WHEN 'shipped' THEN 3 ELSE 0)) ) WHEN 0 THEN 'canceled' WHEN 1
THEN'incomplete' WHEN 2 THEN 'incomplete' WHEN 3 THEN 'fulfilled' ELSE 'void') as
order_state FROM events_registrations GROUP BY order_id ) o_states WHERE o_states.order_id =
o.order_id;
Oracle 9 does support SQL92-compliant CASE WHEN. It's much more clear than
using cryptic decode to me.
See http://www.postgresql.org/idocs/index.php?functions-conditional.html
Hope this works.
-Roberto
P.S.: Some indenting goes a long way through helping to understand your
code.
--
+----| Roberto Mello - http://www.brasileiro.net/ |------+
+ Computer Science Graduate Student, Utah State University +
+ USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ +
"Hello, World!" 17 Errors, 31 Warnings....