From: Roberto Mello <rmello@cc.usu.edu>
To: mgeddert <mgeddert@mbseminary.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: please help with converting a view in oracle into postgresql readably code
Date: Wed, 2 Oct 2002 00:12:07 -0600
Message-ID: <20021002061207.GA2075@cc.usu.edu> (raw)
In-Reply-To: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA>
References: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA>
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....
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rmello@cc.usu.edu, mgeddert@mbseminary.com
Subject: Re: please help with converting a view in oracle into postgresql readably code
In-Reply-To: <20021002061207.GA2075@cc.usu.edu>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox