Home » SQL & PL/SQL » SQL & PL/SQL » Creating a Calendar (merged 3 topics)
Creating a Calendar (merged 3 topics) [message #259798] Thu, 16 August 2007 09:26 Go to next message
vikky538
Messages: 2
Registered: August 2007
Junior Member
hi friends am new to this forum am unable to find .the code for calender especially in SQL(only in SQL ) so plz if any body helps thats a gr8 thing for me..plz post u r replies...with the relevent results...thnk u very much....if u wanna mail me u can mail me to mail id........

Report message to a moderator

Re: SQL code for calender [message #259800 is a reply to message #259798] Thu, 16 August 2007 09:32 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I have no idea whatsoever what you mean by "calendar". Will this do ?

select to_date('01-jan-2007', 'DD-MON-YYYY')+level-1 as thedate
from dual
connect by level <= 365

Report message to a moderator

Re: SQL code for calender [message #259801 is a reply to message #259798] Thu, 16 August 2007 09:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Don't use IM speak use standard english.
Please search before posting, such a question was already asked many times.

Regards
Michel

[Updated on: Thu, 16 August 2007 09:52]

Report message to a moderator

Generate Sequence number using SQL query in Oracle 9i [message #259948 is a reply to message #259798] Thu, 16 August 2007 23:15 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi

How to generate Sequqnce number using SQL query. I dont want to use any sequence object. Also I dont want to use any system table. The query should only use the dual table to generate sequence number.

Thanks
Sam

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #259949 is a reply to message #259948] Thu, 16 August 2007 23:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
The homework forum is near the bottom of this page.
Please stop posting homework assignments here.

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #259952 is a reply to message #259948] Thu, 16 August 2007 23:27 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Thanks for the reply,
But its not a home work assignment. I have to generate month list from Jan - Dec using SQL query in my report. So if I get a logic of generation of sequence number, I can generate the list of months.

Help me if you know the answer otherwise dont criticize.

Thanks.

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #259953 is a reply to message #259948] Thu, 16 August 2007 23:36 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
search this forum for keywords
dual connect rownum
focus on posts by Michel Cadot

>Help me if you know the answer otherwise dont criticize.
If you WANT answers, don't critcize.

[Updated on: Thu, 16 August 2007 23:37] by Moderator

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #259967 is a reply to message #259952] Fri, 17 August 2007 01:02 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I have to generate month list from Jan - Dec using SQL query in my report

So why don't you ask this instead of another question?

Search also for "row generator".
Moreover there was a topic on calendar yesterday, don't you see it?

Regards
Michel

Report message to a moderator

Re: SQL code for calender [message #259975 is a reply to message #259801] Fri, 17 August 2007 01:19 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
A simple search on calendar revealed this old thread:
calendar table - An AskTom solution.

MHE

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #260093 is a reply to message #259967] Fri, 17 August 2007 07:17 Go to previous messageGo to next message
samyugtha
Messages: 14
Registered: November 2005
Junior Member
Hi

Thanks for the reply.

My query to generate Month from Jan - Dec is,

select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val from dual connect by rownum <= 12;


Thanks
Sam

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #260096 is a reply to message #260093] Fri, 17 August 2007 07:23 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member
You can use level instead of rownum also just like the below:
select TO_CHAR(TO_DATE(level,'MM'),'Mon') Month_Val from dual connect by level <= 12;


Cheers
Sanka

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #260116 is a reply to message #260096] Fri, 17 August 2007 08:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Samyugtha, Sanka,

Any of your quries works in 9i.
SQL> select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val from dual connect by rownum <= 12;
MON
---
Jan

1 row selected.

SQL> select TO_CHAR(TO_DATE(level,'MM'),'Mon') Month_Val from dual connect by level <= 12;
MON
---
Jan

1 row selected.

You have to use:
SQL> select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val 
  2  from (select 1 from dual  connect by rownum <= 12)
  3  /
MON
---
Jan
Fev
Mar
Avr
Mai
Jun
Jul
Aou
Sep
Oct
Nov
Dec

12 rows selected.

SQL> select TO_CHAR(TO_DATE(rownum,'MM'),'Mon') Month_Val 
  2  from (select 1 from dual  connect by level <= 12)
  3  /
MON
---
Jan
Fev
Mar
Avr
Mai
Jun
Jul
Aou
Sep
Oct
Nov
Dec

12 rows selected.

Regards
Michel

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #260120 is a reply to message #260116] Fri, 17 August 2007 09:01 Go to previous messageGo to next message
sanka_yanka
Messages: 184
Registered: October 2005
Location: Kolkata
Senior Member
I am using 10g and getting the desire result just using the query:
select TO_CHAR(TO_DATE(level,'MM'),'Mon') Month_Val 
from dual connect by level <= 12;


I've also checked in Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production. It works fine Michel.

Cheers
Sanka

Report message to a moderator

Re: Generate Sequence number using SQL query in Oracle 9i [message #260125 is a reply to message #260120] Fri, 17 August 2007 09:20 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The execution I posted came from a 9.2.0.8.
Algorithm changed between 9i and 10g and make it work in 10g but didn't in 9i.

Regards
Michel

Report message to a moderator

creating a calendar [message #260154 is a reply to message #259798] Fri, 17 August 2007 10:59 Go to previous messageGo to next message
vasudhak
Messages: 1
Registered: August 2007
Junior Member
Hi,
I have been given a SQL query which should generate a calendar i.e. from today's date(sysdate) till the same date of next year.
Can anyone give me an idea as to how I write it ?
I have seen a query which used a view - all_objects.
Can we write it using dual ? When I wrote a query using dual, it isn't working.



Can anyone help me out ?
I don't think we have any inbuilt function to create a calendar.......

[Updated on: Fri, 17 August 2007 11:09]

Report message to a moderator

Re: creating a calendar [message #260166 is a reply to message #260154] Fri, 17 August 2007 11:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2 topics on calendar in 2 days, you are the third one.
I close this topic, just have a look at the other ones.

[Edit: reopen after merge]

Regards
Michel

[Updated on: Fri, 17 August 2007 11:16]

Report message to a moderator

Re: creating a calendar [message #260634 is a reply to message #260154] Mon, 20 August 2007 09:31 Go to previous messageGo to next message
vikky538
Messages: 2
Registered: August 2007
Junior Member
ya buddy we hav to use rownum and we can make the code for the calender..but we need t use the basic query statements...dats the thing..
neway..thanks for u r participation....nice idea u got....keep it up....byeee

Report message to a moderator

Re: creating a calendar [message #260654 is a reply to message #260634] Mon, 20 August 2007 11:06 Go to previous message
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Take care, I don't think you get an answer if you post a question with this language.

Regards
Michel

Report message to a moderator

Previous Topic: Loading external library
Next Topic: querying exception records
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 25 01:01:59 CDT 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.