Date: Wed, 2 Oct 2002 00:12:07 -0600
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
Message-ID: <20021002061207.GA2075@cc.usu.edu>
References: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
In-Reply-To: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA>
User-Agent: Mutt/1.4i

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....