Home » SQL & PL/SQL » SQL & PL/SQL » Function help
Function help [message #231969] Wed, 18 April 2007 23:55 Go to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

Dear Folks,

I'm getting error while trying to create this Function.

please help me.

CREATE or replace FUNCTION fGetNoOfHoliDays (inSubOrgId VARCHAR2, inFromDate DATE, inToDate DATE)
RETURN number
AS
dtCurrDate DATE;
intNoOfHoliDays number(19);
intCurrHoliDays number(19);
BEGIN
SELECT inFromDate into dtCurrDate from dual;
SELECT 0 into intNoOfHoliDays from dual;
SELECT 0 into intCurrHoliDays from dual;
while(1=1)
loop
DELETE from tblDaysList;
WHILE dtCurrDate <= inToDate
loop
INSERT INTO tblDaysList VALUES (dtCurrDate, 0);
SELECT dtCurrDate+1 into dtCurrDate from dual;
ENDloop;
UPDATE tblDaysList SET IsHoliday = 1 where exists (select * from
tblDaysList dl INNER JOIN GaTable058 sr ON dl.Dates BETWEEN sr.Column005 AND sr.Column006 AND
Column009='N' AND Column001=inSubOrgId );
UPDATE tblDaysList SET IsHoliday = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT nh.Column002 HoliDay
FROM GaTable057 ch LEFT OUTER JOIN GaTable055 nh ON ch.Column003=nh.Column001
AND ch.Column001=inSubOrgId AND nh.Column007='Y' AND ch.Column004='N') nh
ON dl.Dates=nh.HoliDay AND dl.IsHoliday=0);
UPDATE tblDaysList SET IsHoliDay = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT to_date((to_char(to_char(inFromDate,'yyyy') ) || '-' ||
to_char(fh.Column004) || '-' || to_char(fh.Column003) ),'rrrr-mm-dd'
) HoliDay
FROM GaTable057 ch INNER JOIN GaTable054 fh ON ch.Column003=fh.Column001
AND ch.Column004='F' AND ch.Column001=inSubOrgId AND fh.Column007='Y'
) fh
ON dl.Dates = fh.HoliDay AND dl.IsHoliDay=0);
UPDATE tblDaysList SET IsHoliDay = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT to_date((to_char(to_char(inToDate,'yyyy')) || '-' ||
to_char(fh.Column004)|| '-' || to_char(fh.Column003)),'rrrr-mm-dd'
) HoliDay
FROM GaTable057 ch INNER JOIN GaTable054 fh ON ch.Column003=fh.Column001
AND ch.Column004='F' AND ch.Column001=inSubOrgId AND fh.Column007='Y'
) fh
ON dl.Dates = fh.HoliDay AND dl.IsHoliDay=0);
SELECT COUNT(IsHoliday) into intCurrHoliDays FROM tblDaysList WHERE IsHoliDay=1;
IF (intCurrHoliDays = 0) then
exit;
ELSE
BEGIN
SELECT intNoOfHoliDays + intCurrHoliDays into intNoOfHoliDays from dual;
SELECT inToDate+1 into dtCurrDate from dual;
SELECT dtCurrDate into inFromDate from dual;
SELECT inToDate+intNoOfHoliDays into inToDate from dual;
end;
end if;
ENDloop;
RETURN intNoOfHoliDays;
END fGetNoOfHoliDays;



error is like this :

LINE/COL ERROR
-------- -----------------------------------------------------------------
59/6 PLS-00103: Encountered the symbol "FGETNOOFHOLIDAYS" when
expecting one of the following:loop

Re: Function help [message #231970 is a reply to message #231969] Wed, 18 April 2007 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
So which is line 59?
Re: Function help [message #231971 is a reply to message #231970] Thu, 19 April 2007 00:03 Go to previous messageGo to next message
shanthkumaar
Messages: 156
Registered: February 2007
Location: india,chennai
Senior Member

hi,

is it ENDloop; ?

i guess it is END LOOP


regards
shanth

[Updated on: Thu, 19 April 2007 00:06]

Report message to a moderator

Re: Function help [message #231989 is a reply to message #231969] Thu, 19 April 2007 00:48 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

CREATE or replace FUNCTION fGetNoOfHoliDays (inSubOrgId VARCHAR2, inFromDate DATE, inToDate DATE)
RETURN number
AS
dtCurrDate DATE;
intNoOfHoliDays number(19);
intCurrHoliDays number(19);
BEGIN
SELECT inFromDate into dtCurrDate from dual;
SELECT 0 into intNoOfHoliDays from dual;
SELECT 0 into intCurrHoliDays from dual;
while 1=1
loop
DELETE from tblDaysList;
WHILE dtCurrDate <= inToDate
loop
INSERT INTO tblDaysList VALUES (dtCurrDate, 0);
SELECT dtCurrDate+1 into dtCurrDate from dual;
END loop;
UPDATE tblDaysList SET IsHoliday = 1 where exists (select * from
tblDaysList dl INNER JOIN GaTable058 sr ON dl.Dates BETWEEN sr.Column005 AND sr.Column006 AND
Column009='N' AND Column001=inSubOrgId );
UPDATE tblDaysList SET IsHoliday = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT nh.Column002 HoliDay
FROM GaTable057 ch LEFT OUTER JOIN GaTable055 nh ON ch.Column003=nh.Column001
AND ch.Column001=inSubOrgId AND nh.Column007='Y' AND ch.Column004='N') nh
ON dl.Dates=nh.HoliDay AND dl.IsHoliday=0);
UPDATE tblDaysList SET IsHoliDay = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT to_date((to_char(to_char(inFromDate,'year') ) || '-' ||
to_char(fh.Column004) || '-' || to_char(fh.Column003) ),'rrrr-mm-dd'
) HoliDay
FROM GaTable057 ch INNER JOIN GaTable054 fh ON ch.Column003=fh.Column001
AND ch.Column004='F' AND ch.Column001=inSubOrgId AND fh.Column007='Y'
) fh
ON dl.Dates = fh.HoliDay AND dl.IsHoliDay=0);
UPDATE tblDaysList SET IsHoliDay = 1 where exists (select *
FROM tblDaysList dl INNER JOIN
(SELECT to_date((to_char(to_char(inToDate,'year')) || '-' ||
to_char(fh.Column004)|| '-' || to_char(fh.Column003)),'rrrr-mm-dd'
) HoliDay
FROM GaTable057 ch INNER JOIN GaTable054 fh ON ch.Column003=fh.Column001
AND ch.Column004='F' AND ch.Column001=inSubOrgId AND fh.Column007='Y'
) fh
ON dl.Dates = fh.HoliDay AND dl.IsHoliDay=0);
SELECT COUNT(IsHoliday) into intCurrHoliDays FROM tblDaysList WHERE IsHoliDay=1;
IF (intCurrHoliDays = 0) then
exit;
ELSE

SELECT intNoOfHoliDays + intCurrHoliDays into intNoOfHoliDays from dual;
SELECT inToDate+1 into dtCurrDate from dual;
SELECT dtCurrDate into inFromDate from dual;
SELECT inToDate+intNoOfHoliDays into inToDate from dual;

end if;
END loop;
RETURN intNoOfHoliDays;
END;


now the error is :


LINE/COL ERROR
-------- -----------------------------------------------------------------
52/61 PLS-00403: expression 'INFROMDATE' cannot be used as an
INTO-target of a SELECT/FETCH statement

53/75 PLS-00403: expression 'INTODATE' cannot be used as an INTO-target
of a SELECT/FETCH statement
Re: Function help [message #231998 is a reply to message #231989] Thu, 19 April 2007 01:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Did you google for these error messages prior to posting them here? The messages are very clear.
Re: Function help [message #232001 is a reply to message #231969] Thu, 19 April 2007 01:15 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
So which are lines 52 and 53?

Why are you using
SELECT inFromDate into dtCurrDate from dual;
instead of
dtCurrDate := inFromDate;
? The second one is simpler and more efficient.

To your problem, you declared inFromDate and inTodate as IN parameters, however you try to assign it a value
SELECT dtCurrDate into inFromDate from dual;
SELECT inToDate+intNoOfHoliDays into inToDate from dual;

Either remove this assignment or change the type of these parameters to IN OUT.
Re: Function help [message #232040 is a reply to message #232001] Thu, 19 April 2007 02:45 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
sunsanvin, use code tags around your code. I'm not beginning on this one.

MHE
Previous Topic: query
Next Topic: Number to Date
Goto Forum:
  


Current Time: Sat Dec 10 07:06:10 CST 2016

Total time taken to generate the page: 0.06030 seconds