pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedplease help with converting a view in oracle into postgresql readably code
4+ messages / 4 participants
[nested] [flat]
* please help with converting a view in oracle into postgresql readably code
@ 2002-10-02 03:46 Matthew Geddert <geddert@yahoo.com>
2002-10-04 14:17 ` Re: please help with converting a view in oracle into postgresql readably code Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Matthew Geddert @ 2002-10-02 03:46 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: please help with converting a view in oracle into postgresql readably code
2002-10-02 03:46 please help with converting a view in oracle into postgresql readably code Matthew Geddert <geddert@yahoo.com>
@ 2002-10-04 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2002-10-04 14:17 UTC (permalink / raw)
To: Matthew Geddert <geddert@yahoo.com>; +Cc: pgsql-sql
"Matthew Geddert" <geddert@yahoo.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* please help with converting a view in oracle into postgresql readably code
@ 2002-10-02 05:41 mgeddert <mgeddert@mbseminary.com>
2002-10-02 06:12 ` Re: please help with converting a view in oracle into postgresql readably code Roberto Mello <rmello@cc.usu.edu>
0 siblings, 1 reply; 4+ messages in thread
From: mgeddert @ 2002-10-02 05:41 UTC (permalink / raw)
To: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: please help with converting a view in oracle into postgresql readably code
2002-10-02 05:41 please help with converting a view in oracle into postgresql readably code mgeddert <mgeddert@mbseminary.com>
@ 2002-10-02 06:12 ` Roberto Mello <rmello@cc.usu.edu>
0 siblings, 0 replies; 4+ messages in thread
From: Roberto Mello @ 2002-10-02 06:12 UTC (permalink / raw)
To: mgeddert <mgeddert@mbseminary.com>; +Cc: 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....
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2002-10-04 14:17 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-10-02 03:46 please help with converting a view in oracle into postgresql readably code Matthew Geddert <geddert@yahoo.com>
2002-10-04 14:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2002-10-02 05:41 please help with converting a view in oracle into postgresql readably code mgeddert <mgeddert@mbseminary.com>
2002-10-02 06:12 ` Roberto Mello <rmello@cc.usu.edu>
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