Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 3A99C4793AC for ; Fri, 4 Oct 2002 10:22:46 -0400 (EDT) Received: from sss.pgh.pa.us (unknown [192.204.191.242]) by postgresql.org (Postfix) with ESMTP id 19A084771AE for ; Fri, 4 Oct 2002 10:17:35 -0400 (EDT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.5/8.12.5) with ESMTP id g94EHYhR020461; Fri, 4 Oct 2002 10:17:35 -0400 (EDT) To: "Matthew Geddert" Cc: pgsql-sql@postgresql.org Subject: Re: please help with converting a view in oracle into postgresql readably code In-reply-to: <000301c269c6$3cab1000$a6267f18@ULTRAENIGMA> References: <000301c269c6$3cab1000$a6267f18@ULTRAENIGMA> Comments: In-reply-to "Matthew Geddert" message dated "Tue, 01 Oct 2002 20:46:05 -0700" Date: Fri, 04 Oct 2002 10:17:34 -0400 Message-ID: <20460.1033741054@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200210/59 X-Sequence-Number: 9777 "Matthew Geddert" writes: > create or replace view events_orders_states > as > select o.*, > o_states.order_state > from events_orders o, > (select > order_id, > decode (floor(avg (decode (reg_state, > 'canceled', 0, > 'waiting', 1, > 'pending', 2, > 'shipped', 3, > 0))), > 0, 'canceled', > 1, 'incomplete', > 2, 'incomplete', > 3, 'fulfilled', > 'void') as order_state > from events_registrations > group by order_id) o_states > where o_states.order_id = o.order_id; DECODE() is an Oracle-ism. You can use the SQL-standard CASE construct instead. See http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/functions-conditional.html Other than the DECODE() calls this should run fine in PG. regards, tom lane