Home » SQL & PL/SQL » SQL & PL/SQL » create custom function in oracle (merged) (10g)
create custom function in oracle (merged) [message #430278] Mon, 09 November 2009 22:14 Go to next message
shiva_jm
Messages: 9
Registered: November 2009
Junior Member
Any tips on how to create a function that produces the following output. I am new to SQL.

Assuming a default one time value is set '10/10/2009',

function next() should return 10/20/2009, then last day of month, then 11/10/2009, then 11/20/2009, then last day of november and goes on...

So basically I need 10th, 20th and last day of every month when I call the next function Smile

Thank you
create custom function in oracle [message #430280 is a reply to message #430278] Mon, 09 November 2009 22:21 Go to previous messageGo to next message
shiva_jm
Messages: 9
Registered: November 2009
Junior Member
I need a function that produces the following output. I am new to SQL.Appretiate any tips.

Assuming a value '10/10/2009' is passed to function , it must return 10/20/2009 (20th day of month).

If 10/20/2009 is passed to function it must return last day of month (10/31/2009)

if last day of month is passed to function, it must return 10th day of next month.

function next(curr_value) {
return '10/20/2009'
}

So basically I need 10th, 20th and last day of every month when I call the next function Smile, current value is passed in..

Thank you
Re: SQL function [message #430281 is a reply to message #430278] Mon, 09 November 2009 22:22 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
>function next() should return 10/20/2009, then last day of month, then 11/10/2009, then 11/20/2009, then last day of november and goes on

How does function remember/know which answer it gave last to determine which answer to give NEXT?

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/toc.htm

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.
Re: SQL function [message #430283 is a reply to message #430278] Mon, 09 November 2009 22:43 Go to previous messageGo to next message
shiva_jm
Messages: 9
Registered: November 2009
Junior Member
its stored in a table and passed in as parameter
Re: SQL function [message #430284 is a reply to message #430283] Mon, 09 November 2009 22:52 Go to previous messageGo to next message
BlackSwan
Messages: 24908
Registered: January 2009
Senior Member
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions072.htm#i83733
Re: create custom function in oracle (merged) [message #430313 is a reply to message #430278] Tue, 10 November 2009 00:30 Go to previous message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste what you already tried.

You use a CASE expression and TRUNC and LAST_DAY functions for this.

Regards
Michel
Previous Topic: add column dynamically
Next Topic: Trigger for update on each row
Goto Forum:
  


Current Time: Tue Sep 27 16:06:57 CDT 2016

Total time taken to generate the page: 0.11517 seconds