DECLARE a FUNCTION [message #655891] |
Thu, 15 September 2016 09:16 |
|
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 |
cookiemonster
Messages: 13923 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 #655894 is a reply to message #655893] |
Thu, 15 September 2016 10:46 |
cookiemonster
Messages: 13923 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 #655987 is a reply to message #655896] |
Mon, 19 September 2016 08:59 |
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.
|
|
|