From: mgeddert <mgeddert@mbseminary.com>
To: pgsql-sql@postgresql.org
Subject: please help with converting a view in oracle into postgresql readably code
Date: Tue, 1 Oct 2002 22:41:17 -0700
Message-ID: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA> (raw)
Hello,
I am trying to convert an application to postgresql, and am having a
bear of a time converting the following view (from oracle). What it
does, just in case you aren't very familiar with oracle syntax, is group
the average reg_state from the events_registrations table after having
converted the varchar variables present in reg_state, into numbers to
represent the various possibilities). Then, once it has found the
average number for all items in the table events_registrations with the
same order_id number it fills in a human readable word.
Thanks for any help in letting me either know how to create this view,
or to tell me how to convert the variables in the varchar field
reg_state into the numbers I want them to represent which I will be able
to manipulate with things such as floor() and avg().
Matthew Geddert
=====
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;
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: mgeddert@mbseminary.com
Subject: Re: please help with converting a view in oracle into postgresql readably code
In-Reply-To: <000001c269d6$54d29580$a6267f18@ULTRAENIGMA>
* 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