On Tue, Oct 01, 2002 at 10:41:17PM -0700, mgeddert wrote:
>
create or replace view events_orders_states as
select o.*,
o_states.order_state
from events_orders o,
(
SELECT order_id,
CASE (
floor (avg (
CASE reg_state
WHEN 'canceled' THEN 0
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....
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
