OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » Function help

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Function help [message #231969]

Wed, 18 April 2007 23:55

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

Report message to a moderator

Re: Function help [message #231970 is a reply to message #231969]

Wed, 18 April 2007 23:56

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

So which is line 59?

Report message to a moderator

Re: Function help [message #231971 is a reply to message #231970]

Thu, 19 April 2007 00:03

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

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

Report message to a moderator

Re: Function help [message #231998 is a reply to message #231989]

Thu, 19 April 2007 01:11

Frank
Messages: 7901
Registered: March 2000

Senior Member

Did you google for these error messages prior to posting them here? The messages are very clear.

Report message to a moderator

Re: Function help [message #232001 is a reply to message #231969]

Thu, 19 April 2007 01:15

flyboy
Messages: 1903
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.

Report message to a moderator

Re: Function help [message #232040 is a reply to message #232001]

Thu, 19 April 2007 02:45

Maaher
Messages: 7065
Registered: December 2001

Senior Member

sunsanvin, use code tags around your code. I'm not beginning on this one.

MHE

Report message to a moderator

Previous Topic:	query
Next Topic:	Number to Date

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 13 00:07:10 CST 2024