function help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

function help

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-04-18 : 08:20:22

Dear Experts,
I've one function, while creating this, I'm getting error.

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 : LINE/COL ERROR
-------- -----------------------------------------------------------------
59/6 PLS-00103: Encountered the symbol "FGETNOOFHOLIDAYS" when
expecting one of the following:
loop

what is the problem there? please guide me

thanks in advance

Vinod

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-18 : 08:21:39

This is SQL Server related forum. Please post only SQL Server-specific questions here.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

X002548
Not Just a Number

15586 Posts

Posted - 2007-04-18 : 09:42:25

>> while(1=1)

You gotta just love that

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources