From: | Roberto Mello <rmello(at)cc(dot)usu(dot)edu> |
---|---|
To: | mgeddert <mgeddert(at)mbseminary(dot)com> |
Cc: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: please help with converting a view in oracle into postgresql readably code |
Date: | 2002-10-02 06:12:07 |
Message-ID: | 20021002061207.GA2075@cc.usu.edu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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....
From | Date | Subject | |
---|---|---|---|
Next Message | Frederic Logier | 2002-10-02 10:30:14 | split function for pl/pgsql |
Previous Message | mgeddert | 2002-10-02 05:41:17 | please help with converting a view in oracle into postgresql readably code |