Home » SQL & PL/SQL » SQL & PL/SQL » A list of Mounth
A list of Mounth [message #382884] Mon, 26 January 2009 01:12 Go to next message
nanees
Messages: 4
Registered: October 2008
Junior Member
Hi all;

Iwant select A list of Months like this

MONTH_DES MONTH_IND
--------- ----------
JANUARY 1
FEBRUARY 2
MARCH 3
APRIL 4
MAY 5
JUNE 6
JULY 7
AUGUST 8
SEPTEMBER 9
OCTOBER 10
NOVEMBER 11
DECEMBER 12


please help me Sad

Re: A list of Mounth [message #382885 is a reply to message #382884] Mon, 26 January 2009 01:14 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
provide DDL for tables
provide DML for test/sample data
provide sample/desired results

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
So we can help you
Re: A list of Mounth [message #382886 is a reply to message #382884] Mon, 26 January 2009 01:19 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Search this forum for CALENDAR or ROW GENERATOR to find hints for the technique to use.
If you still are stuck, come back and show us what you tried.
Re: A list of Mounth [message #382887 is a reply to message #382884] Mon, 26 January 2009 01:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    months as (
  3      select add_months(trunc(sysdate,'YEAR'),level-1) month
  4      from dual
  5      connect by level <= 12 
  6    )
  7  select to_char(month,'MONTH') month_desc,
  8         to_char(month,'MM') month_idx
  9  from months
 10  order by 2
 11  /
MONTH_DES MO
--------- --
JANUARY   01
FEBRUARY  02
MARCH     03
APRIL     04
MAY       05
JUNE      06
JULY      07
AUGUST    08
SEPTEMBER 09
OCTOBER   10
NOVEMBER  11
DECEMBER  12

Regards
Michel
Re: A list of Mounth [message #383643 is a reply to message #382887] Thu, 29 January 2009 07:50 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Dear Michel Cadot,

No doubt your solution is the best one i appreciate you for your efforts but i am using oracle 8i and these statement is not working well and i found such error
SQL> with 
SP2-0042: unknown command "with" - rest of line ignored.
SQL>   2    months as (
SQL>   3      select add_months(trunc(sysdate,'YEAR'),level-1) month
SQL>   4      from dual
SQL>   5      connect by level <= 12 
SQL>   6    )
SQL>   7  select to_char(month,'MONTH') month_desc,
SQL>   8         to_char(month,'MM') month_idx
SQL>   9  from months
SQL>  10  order by 2
SQL>  11  /
SQL> 

Would you please guide us why? Please don't mind as i am a jounior member but wish to understand this with,level-1 and connect statement. Thanks for your time to read and write.

Muhammad Khalil
Re: A list of Mounth [message #383645 is a reply to message #383643] Thu, 29 January 2009 08:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not available until 9i.

If I were you, I'd upgrade - 8i's totally unsupported.

Failing that, try this:
CREATE TABLE months AS 
SELECT rownum  month_idx,
      to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MONTH')  month_desc
FROM   all_objects
WHERE  rownum <=12;
Re: A list of Mounth [message #383646 is a reply to message #383643] Thu, 29 January 2009 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Before 9i just put the subquery in WITH clause in an inline in FROM and change the row generator to one supported in your version:
SQL> select to_char(month,'MONTH') month_desc,
  2         to_char(month,'MM') month_idx
  3  from (
  4      select add_months(trunc(sysdate,'YEAR'),rownum-1) month
  5      from (select 1 from dual group by cube(1,2,3,4))
  6      where rownum <= 12
  7    )
  8  order by 2
  9  /
MONTH_DES MO
--------- --
JANUARY   01
FEBRUARY  02
MARCH     03
APRIL     04
MAY       05
JUNE      06
JULY      07
AUGUST    08
SEPTEMBER 09
OCTOBER   10
NOVEMBER  11
DECEMBER  12

Regards
Michel
Re: A list of Mounth [message #383649 is a reply to message #383646] Thu, 29 January 2009 08:19 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Quote:

Before 9i


I guess you meant 10g.

Regards

Raj

P.S : I assume you meant the connect by logic
Re: A list of Mounth [message #383652 is a reply to message #383649] Thu, 29 January 2009 08:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I was talking about the WITH clause but you are right the row generator has to be slighty modified for 9i.

Regards
Michel
Re: A list of Mounth [message #383664 is a reply to message #383652] Thu, 29 January 2009 10:21 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Both queries of Mr.Michel Cadot and Mr.JRowbottom are working well in 8i. These queries can solve my problem but i want to learn about row generator for oracle8i. Could anyone give me the link of tutorial. A lot of thanks for your so quick response.
Re: A list of Mounth [message #383671 is a reply to message #383664] Thu, 29 January 2009 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Go to General forum and read Puzzle sticky.

Regards
Michel
Re: A list of Mounth [message #384041 is a reply to message #383671] Sun, 01 February 2009 23:36 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Mr.Michel Cadot. I tried to find but not success. Could you please send me the exact link.
Re: A list of Mounth [message #384052 is a reply to message #384041] Mon, 02 February 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
General forum.
First topic: "Puzzle menu"
First puzzle: "Puzzle n00 - Row generator *"

Regards
Michel
Re: A list of Mounth [message #384150 is a reply to message #382884] Mon, 02 February 2009 06:18 Go to previous messageGo to next message
nanees
Messages: 4
Registered: October 2008
Junior Member
Dear michel Cadot

This solution
-----------------------------------------------------------------
select to_char(month,'MONTH') month_desc,
to_char(month,'MM') month_idx
from (
select add_months(trunc(sysdate,'YEAR'),rownum-1) month
from (select 1 from dual group by cube(1,2,3,4))
where rownum <= 12
)
order by 2
-----------------------------------------------------------------is the best one and working whithout errors i'm using oracle 6i


Thank you very mutch Smile
Re: A list of Mounth [message #384312 is a reply to message #384150] Tue, 03 February 2009 04:05 Go to previous messageGo to next message
mkhalil
Messages: 108
Registered: July 2006
Location: NWFP Peshawar Pakistan
Senior Member
Thanks Michel you are greate.
Re: A list of Mounth [message #384337 is a reply to message #384150] Tue, 03 February 2009 07:05 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's certainly the most flexible solution, and also the one requiring the least database objects (ie zero).
It's not the most performant:
drop table months;

CREATE TABLE months AS 
SELECT rownum  month_idx,
      to_char(add_months(trunc(sysdate,'YEAR'),rownum-1),'MONTH')  month_desc
FROM   all_objects
WHERE  rownum <=12;
 
declare 
  v_iter   pls_integer := 100000;
  v_time   pls_integer;
  v_val1   varchar2(30);
  v_val2   varchar2(30);
  
begin

  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop

    for rec in (select to_char(month,'MONTH') month_desc,
                       to_char(month,'MM') month_idx
                from (select add_months(trunc(sysdate,'YEAR'),rownum-1) month
                      from (select 1 from dual group by cube(1,2,3,4))
                      where rownum <= 12 )
                order by 2) loop
      v_val1 := rec.month_desc;
      v_val2 := rec.month_idx;
    end loop;
  end loop;
  
  dbms_output.put_line('Cursor 1: '||to_char(dbms_utility.get_time - v_time));

  v_time := dbms_utility.get_time;
  
  for i in 1..v_iter loop

    for rec in (select month_desc,
                       month_idx
                from   months
                order by 2) loop
      v_val1 := rec.month_desc;
      v_val2 := rec.month_idx;
    end loop;
  end loop;
  
  dbms_output.put_line('Cursor 2: '||to_char(dbms_utility.get_time - v_time));
  
end;
/
Cursor 1: 3152
Cursor 2: 2209


I didn't have an 8i Db to hand, so I had to test against 9.2.0.8
Obviously, whether this matters or not depends on how often it will get executed. If you only use it infrequently, then you'll never notice.
If you join to it as a data source in a big query, then you may well notice the difference.
Previous Topic: using cursor
Next Topic: to_char function help
Goto Forum:
  


Current Time: Thu Dec 08 08:34:14 CST 2016

Total time taken to generate the page: 0.06457 seconds