Home » SQL & PL/SQL » SQL & PL/SQL » DECLARE a FUNCTION
DECLARE a FUNCTION [message #655891] Thu, 15 September 2016 09:16 Go to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Documentation says that this should work if you DECLARE the FUNCTION at the end of the DECLARE section. I am well aware that you can CREATE a function and there are other ways to do this. But I would like to see if I can get this to work if nothing else but for future reference.

I get a FUNCTION cannot be used error invalid identifier.

DECLARE

FUNCTION GetYearMonth
(
  p_Year NUMBER,
  p_Month NUMBER
)
RETURN VARCHAR2
AS
  v_YearMonth VARCHAR2(50);
  
  v_YearTemp  NUMBER;
  v_MonthTemp NUMBER;
BEGIN
  v_YearTemp := p_Year;
  v_MonthTemp := p_Month;

  IF v_MonthTemp = 0 THEN
    v_MonthTemp := 12;
    v_YearTemp := p_Year - 1;
  END IF;
  
  IF v_MonthTemp = -1 THEN
    v_MonthTemp := 11;
    v_YearTemp := p_Year - 1;
  END IF;
  
  IF v_MonthTemp = -2 THEN
    v_MonthTemp := 10;
    v_YearTemp := p_Year - 1;
  END IF;
  
  IF LENGTH(CAST(v_MonthTemp AS VARCHAR2)) = 1 THEN
    v_YearMonth := CAST(v_YearTemp AS VARCHAR2) || '/0' || CAST(v_MonthTemp as VARCHAR2);
  ELSE
    v_YearMonth := CAST(v_YearTemp AS VARCHAR2) || '/' || CAST(v_MonthTemp as VARCHAR2);
  END IF;
    
  RETURN v_YearMonth;

END;

BEGIN

  OPEN :p_RefCursor FOR
  SELECT distinct YearMonth, TotUsage, Name
  FROM
  (SELECT GetYearMonth(Year, Month) as YearMonth, TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND   Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
  UNION 
  SELECT GetYearMonth(Year, Month - 1) as YearMonth, LastMonth AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
  UNION
  SELECT GetYearMonth(Year, Month - 2) as YearMonth, TwoMonthAgo AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv
  UNION
  SELECT GetYearMonth(Year, Month - 3) as YearMonth, ThreeMonthAgo AS TotUsage, Name FROM HWL_Variance Where AcctNo = :p_AcctNo AND Route = :p_Route AND HasAuxMeter = :p_HasAuxMeter AND Srv = :p_Srv) 
  ORDER BY YearMonth;

END;
Re: DECLARE a FUNCTION [message #655892 is a reply to message #655891] Thu, 15 September 2016 10:22 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
The function is declared, but SQL can't see it. If you referred to it with PL/SQL code in the executable section that would be fine but SQL needs an actual created function.
I did show you how to do what you want without a function in your other thread.
Re: DECLARE a FUNCTION [message #655893 is a reply to message #655892] Thu, 15 September 2016 10:28 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
I understood your other post...like I said I am asking this for future reference. What do you mean when you say "If You Referred in executable section" How do I do that?
Re: DECLARE a FUNCTION [message #655894 is a reply to message #655893] Thu, 15 September 2016 10:46 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I mean, not in SQL statement. So this:
BEGIN

  variable := GetYearMonth(1, 2);

END;

Would compile and work, but is no use to you.
If you want to use your own functions in select statements then they have to created, as stand alone functions or as part of a package.
Re: DECLARE a FUNCTION [message #655895 is a reply to message #655894] Thu, 15 September 2016 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Or, if you are in 12c, something you should post, you can declare it in SQL statement itself.

Re: DECLARE a FUNCTION [message #655896 is a reply to message #655894] Thu, 15 September 2016 11:07 Go to previous messageGo to next message
derrellgore
Messages: 20
Registered: July 2016
Junior Member
Gotcha! That makes sense now. Thanks
Re: DECLARE a FUNCTION [message #655987 is a reply to message #655896] Mon, 19 September 2016 08:59 Go to previous message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
That being said, I would not call any function. The code for the function would be very easy to setup in the select using case statements. There is no need to call a separate function.
Previous Topic: SQL%ROWCOUNT
Next Topic: Materialised views not refreshing
Goto Forum:
  


Current Time: Thu Mar 28 16:40:26 CDT 2024