Thread: please help with converting a view in oracle into postgresql readably code
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,(selectorder_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_statefromevents_registrationsgroup by order_id) o_states where o_states.order_id = o.order_id;
Re: please help with converting a view in oracle into postgresql readably code
From
Roberto Mello
Date:
On Tue, Oct 01, 2002 at 10:41:17PM -0700, mgeddert wrote: > create or replace view events_orders_states asselect o.*,o_states.order_state from events_orders o, ( SELECTorder_id, CASE ( floor (avg ( CASE reg_state WHEN 'canceled' THEN0 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....
Re: PROBLEM SOLVED RE: please help with converting a view in oracle into postgresql readably code
From
Roberto Mello
Date:
On Tue, Oct 01, 2002 at 11:55:14PM -0700, mgeddert wrote: > Robert, > > Thanks for the help, I kept on playing with what you gave me, and after > removing one () pair and adding the ENDs to the CASE WHENs it works! > Thank you so much for the help, I have been very frustrated with this > for a number of days now. Argh, that's right, I forgot the ENDs. Ugh! Shame on me. Glad that's working for you now. -Roberto -- +----| Roberto Mello - http://www.brasileiro.net/ |------+ + Computer Science Graduate Student, Utah State University + + USU Free Software & GNU/Linux Club - http://fslc.usu.edu/ + Q: Heard about the <ethnic> who couldn't spell? A: He spent the night in a warehouse.