Thread: sorting months according to fiscal year
Hi I have to rearrange the months according to the fiscal year i.e from April to march and use the same in the order by clause of a query. I have written the following postgresql function for the same, but to_number is returning an error. Can you please tell me where i'm going wrong? Instead of the function to_number can you suggest any other function that will convert a particular month to its corresponding month number(ex: april=4 or jan=1) CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$ DECLARE BEGIN CASE WHEN 4 THEN 1 WHEN 5 THEN 2 WHEN 6 THEN 3 WHEN 7 THEN 4 WHEN 8 THEN 5 WHEN 9 THEN 6 WHEN 10 THEN 7 WHEN 11 THEN 8 WHEN 12 THEN 9 WHEN 1 THEN10 WHEN 2 THEN 11 WHEN 3 THEN 12 ELSE 0 END; $$ LANGUAGE plpgsql; -- View this message in context: http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Hello 2011/8/22 Enzen user <anushka.achaiah@enzentech.com>: > Hi > I have to rearrange the months according to the fiscal year i.e from April > to march and use the same in the order by clause of a query. > I have written the following postgresql function for the same, but to_number > is returning an error. > Can you please tell me where i'm going wrong? > Instead of the function to_number can you suggest any other function that > will convert a particular month to its corresponding month number(ex: > april=4 or jan=1) > > > CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$ you cannot use to_number function inside param's list > DECLARE > > BEGIN > > CASE WHEN 4 THEN 1 > WHEN 5 THEN 2 > WHEN 6 THEN 3 > WHEN 7 THEN 4 > WHEN 8 THEN 5 > WHEN 9 THEN 6 > WHEN 10 THEN 7 > WHEN 11 THEN 8 > WHEN 12 THEN 9 > WHEN 1 THEN 10 > WHEN 2 THEN 11 > WHEN 3 THEN 12 > ELSE 0 > END; > > > > $$ LANGUAGE plpgsql; > CREATE OR REPLACE FUNCTION sort_month(int) RETURNS int -- is wrong to use numeric here AS $$... $$ LANGUAGE sql; SELECT sort_ month(to_nuber(...)) Regards Pavel Stehule > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
On 23/08/11 01:27, Enzen user wrote: > Hi > I have to rearrange the months according to the fiscal year i.e from April > to march and use the same in the order by clause of a query. > I have written the following postgresql function for the same, but to_number > is returning an error. > Can you please tell me where i'm going wrong? > Instead of the function to_number can you suggest any other function that > will convert a particular month to its corresponding month number(ex: > april=4 or jan=1) > > > CREATE FUNCTION sort_month(to_number('April','MM')) RETURNS numeric AS $$ > DECLARE > > BEGIN > > CASE WHEN 4 THEN 1 > WHEN 5 THEN 2 > WHEN 6 THEN 3 > WHEN 7 THEN 4 > WHEN 8 THEN 5 > WHEN 9 THEN 6 > WHEN 10 THEN 7 > WHEN 11 THEN 8 > WHEN 12 THEN 9 > WHEN 1 THEN 10 > WHEN 2 THEN 11 > WHEN 3 THEN 12 > ELSE 0 > END; > > > > $$ LANGUAGE plpgsql; > > > > -- > View this message in context: http://postgresql.1045698.n5.nabble.com/sorting-months-according-to-fiscal-year-tp4723409p4723409.html > Sent from the PostgreSQL - sql mailing list archive at Nabble.com. > You might find the following faster... DROP FUNCTION IF EXISTS sort_mont ( to_number int ) ; CREATE FUNCTION sort_mont ( to_number int ) RETURNS numeric LANGUAGE plpgsql AS $$ BEGIN RETURN 1 + (to_number + 8) % 12; END; $$; SELECT sort_mont(1); SELECT sort_mont(12); /// output.. gavin=> \i modulus_stored_proc.sql DROP FUNCTION CREATE FUNCTION sort_mont ----------- 10 (1 row) sort_mont ----------- 9 (1 row) gavin=>