473,574 Members | 2,389 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

function workingdays problem

Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
Thank you very much!!!!!

Ian

Aug 25 '06 #1
8 3930

Ian Mackenzie wrote:
Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
Thank you very much!!!!!

Ian
1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.

B.

Aug 25 '06 #2

Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
Thank you very much!!!!!

Ian

1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.

B.
Thanks Brian!!!

Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1

Trying this...

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;

RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
DETERMINISTIC NO EXTERNAL ACTION


BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601

SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601

END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >

Any help would be appreciated!!!! !!!!!1

Thanks

Ian

Aug 25 '06 #3
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys
>
I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:
>
CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0
>
WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;
>
SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE
>
RETURN DAYCOUNT
END
>
Does anyone know what I could do to fix this?
>
These are the errors I get:
>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601
>
SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
>
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
>
>
Thank you very much!!!!!
>
Ian
1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.

B.

Thanks Brian!!!

Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1

Trying this...

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;

RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
DETERMINISTIC NO EXTERNAL ACTION


BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601

SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601

END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >

Any help would be appreciated!!!! !!!!!1

Thanks

Ian
Put a \ after each line. It looks like it is treating each line
separately.

Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:

DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)

if it is in the same year. For multiple years, a more complex solution
may be required.

And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)

B.

Aug 25 '06 #4
You might want to put two hyphens (dashes) after each semicolon, except
for the last one, such as

DECLARE DAYCOUNT INTEGER DEFAULT 0;--

(Notice also how you can initialize a value without having to use a
separate SET statement).

Also, do you have a holidays table? Depdending on your needs, you may
need to consider holidays as well.

As to the loop, in my version of a workdays-type function, I've done
this:

<SNIP>
SET V_CURDAY = DAYS(P_START_DT );--
....
IF (V_END_DT >= P_START_DT) THEN
SET V_DAYS = DAYS(V_END_DT) - DAYS(P_START_DT );--
WHILE V_CURDAY <= DAYS(V_END_DT) DO
IF DAYOFWEEK(DATE( V_CURDAY)) IN (1, 7) THEN
SET V_DAYS = V_DAYS - 1;--
END IF;--
<SNIP>

--Jeff

Brian Tkatch wrote:
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT


Thank you very much!!!!!

Ian
>
1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.
>
B.
Thanks Brian!!!

Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1

Trying this...

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;

RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
DETERMINISTIC NO EXTERNAL ACTION


BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601

SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601

END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >

Any help would be appreciated!!!! !!!!!1

Thanks

Ian

Put a \ after each line. It looks like it is treating each line
separately.

Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:

DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)

if it is in the same year. For multiple years, a more complex solution
may be required.

And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)

B.
Aug 25 '06 #5
Thanks guys!!

Got it working after a little tweaking.
jefftyzzer wrote:
You might want to put two hyphens (dashes) after each semicolon, except
for the last one, such as

DECLARE DAYCOUNT INTEGER DEFAULT 0;--

(Notice also how you can initialize a value without having to use a
separate SET statement).

Also, do you have a holidays table? Depdending on your needs, you may
need to consider holidays as well.

As to the loop, in my version of a workdays-type function, I've done
this:

<SNIP>
SET V_CURDAY = DAYS(P_START_DT );--
...
IF (V_END_DT >= P_START_DT) THEN
SET V_DAYS = DAYS(V_END_DT) - DAYS(P_START_DT );--
WHILE V_CURDAY <= DAYS(V_END_DT) DO
IF DAYOFWEEK(DATE( V_CURDAY)) IN (1, 7) THEN
SET V_DAYS = V_DAYS - 1;--
END IF;--
<SNIP>

--Jeff

Brian Tkatch wrote:
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys
>
I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:
>
CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0
>
WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;
>
SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE
>
RETURN DAYCOUNT
END
>
Does anyone know what I could do to fix this?
>
These are the errors I get:
>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601
>
SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
>
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
>
>
Thank you very much!!!!!
>
Ian

1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.

B.
>
Thanks Brian!!!
>
Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1
>
Trying this...
>
------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
>
DETERMINISTIC NO EXTERNAL ACTION
>
>
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;
>
WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;
>
SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;
>
RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
>
>
DETERMINISTIC NO EXTERNAL ACTION
>
>
>
>
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601
>
SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601
>
END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
>
END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601
>
RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601
>
END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >
>
Any help would be appreciated!!!! !!!!!1
>
Thanks
>
Ian
Put a \ after each line. It looks like it is treating each line
separately.

Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:

DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)

if it is in the same year. For multiple years, a more complex solution
may be required.

And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)

B.
Aug 29 '06 #6

Ian Mackenzie wrote:
Thanks guys!!

Got it working after a little tweaking.
jefftyzzer wrote:
You might want to put two hyphens (dashes) after each semicolon, except
for the last one, such as

DECLARE DAYCOUNT INTEGER DEFAULT 0;--

(Notice also how you can initialize a value without having to use a
separate SET statement).

Also, do you have a holidays table? Depdending on your needs, you may
need to consider holidays as well.

As to the loop, in my version of a workdays-type function, I've done
this:

<SNIP>
SET V_CURDAY = DAYS(P_START_DT );--
...
IF (V_END_DT >= P_START_DT) THEN
SET V_DAYS = DAYS(V_END_DT) - DAYS(P_START_DT );--
WHILE V_CURDAY <= DAYS(V_END_DT) DO
IF DAYOFWEEK(DATE( V_CURDAY)) IN (1, 7) THEN
SET V_DAYS = V_DAYS - 1;--
END IF;--
<SNIP>

--Jeff

Brian Tkatch wrote:
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT


Thank you very much!!!!!

Ian
>
1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.
>
B.

Thanks Brian!!!

Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1

Trying this...

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION


BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;

RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER


DETERMINISTIC NO EXTERNAL ACTION




BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601

SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601

END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >

Any help would be appreciated!!!! !!!!!1

Thanks

Ian
>
Put a \ after each line. It looks like it is treating each line
separately.
>
Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:
>
DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)
>
if it is in the same year. For multiple years, a more complex solution
may be required.
>
And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)
>
B.
Want to post what you cam up with? Perhaps someone else may find it
useful.

B.

Aug 29 '06 #7

Brian Tkatch wrote:
Ian Mackenzie wrote:
Thanks guys!!

Got it working after a little tweaking.
jefftyzzer wrote:
You might want to put two hyphens (dashes) after each semicolon, except
for the last one, such as
>
DECLARE DAYCOUNT INTEGER DEFAULT 0;--
>
(Notice also how you can initialize a value without having to use a
separate SET statement).
>
Also, do you have a holidays table? Depdending on your needs, you may
need to consider holidays as well.
>
As to the loop, in my version of a workdays-type function, I've done
this:
>
<SNIP>
SET V_CURDAY = DAYS(P_START_DT );--
...
IF (V_END_DT >= P_START_DT) THEN
SET V_DAYS = DAYS(V_END_DT) - DAYS(P_START_DT );--
WHILE V_CURDAY <= DAYS(V_END_DT) DO
IF DAYOFWEEK(DATE( V_CURDAY)) IN (1, 7) THEN
SET V_DAYS = V_DAYS - 1;--
END IF;--
<SNIP>
>
--Jeff
>
Brian Tkatch wrote:
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys
>
I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:
>
CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0
>
WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;
>
SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE
>
RETURN DAYCOUNT
END
>
Does anyone know what I could do to fix this?
>
These are the errors I get:
>
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601
>
SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
>
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT
>
>
Thank you very much!!!!!
>
Ian

1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.

B.
>
Thanks Brian!!!
>
Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1
>
Trying this...
>
------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
>
DETERMINISTIC NO EXTERNAL ACTION
>
>
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;
>
WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;
>
SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;
>
RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER
>
>
DETERMINISTIC NO EXTERNAL ACTION
>
>
>
>
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601
>
SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601
>
END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601
>
END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601
>
RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601
>
END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601
>
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >
>
Any help would be appreciated!!!! !!!!!1
>
Thanks
>
Ian

Put a \ after each line. It looks like it is treating each line
separately.

Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:

DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)

if it is in the same year. For multiple years, a more complex solution
may be required.

And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)

B.

Want to post what you cam up with? Perhaps someone else may find it
useful.

B.
It may not look too great, but it works! if the start and end dates are
on the same day, it counts 1 day...

CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER DEFAULT 0;--
DECLARE STARTDAYS INTEGER;--
DECLARE ENDDAYS INTEGER;--

SET STARTDAYS = DAYS(STARTDATE) ;--
SET ENDDAYS = DAYS(ENDDATE);--

IF (STARTDAYS <= ENDDAYS) THEN
WHILE (STARTDAYS <= ENDDAYS) DO
IF (DAYOFWEEK_ISO( DATE(STARTDAYS) ) < 6) THEN
IF ((STARTDAYS = ENDDAYS) AND (DAYCOUNT = 0)) THEN
SET DAYCOUNT = 1;--
END IF;--

IF (STARTDAYS < ENDDAYS) THEN
SET DAYCOUNT = DAYCOUNT + 1;--
END IF;--
END IF;--

SET STARTDAYS = STARTDAYS + 1;--
END WHILE;--
ELSE
SET DAYCOUNT = 0;--
END IF;--

RETURN DAYCOUNT;--
END

Hope you find it userful...

Ian

Sep 1 '06 #8

Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Thanks guys!!
>
Got it working after a little tweaking.
>
>
jefftyzzer wrote:
You might want to put two hyphens (dashes) after each semicolon, except
for the last one, such as

DECLARE DAYCOUNT INTEGER DEFAULT 0;--

(Notice also how you can initialize a value without having to use a
separate SET statement).

Also, do you have a holidays table? Depdending on your needs, you may
need to consider holidays as well.

As to the loop, in my version of a workdays-type function, I've done
this:

<SNIP>
SET V_CURDAY = DAYS(P_START_DT );--
...
IF (V_END_DT >= P_START_DT) THEN
SET V_DAYS = DAYS(V_END_DT) - DAYS(P_START_DT );--
WHILE V_CURDAY <= DAYS(V_END_DT) DO
IF DAYOFWEEK(DATE( V_CURDAY)) IN (1, 7) THEN
SET V_DAYS = V_DAYS - 1;--
END IF;--
<SNIP>

--Jeff

Brian Tkatch wrote:
Ian Mackenzie wrote:
Brian Tkatch wrote:
Ian Mackenzie wrote:
Hi Guys

I am VERY new to DB2 and have created a workingdays function to return
the working days between 2 dates, but I get some compiler errors when
running it:

CREATE FUNCTION WORKINGDAYS (STARTDATE, ENDDATE DATE) RETURNS INTEGER
DETERMINISTIC NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
SET (DAYCOUNT) = 0

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY)
END WHILE

RETURN DAYCOUNT
END

Does anyone know what I could do to fix this?

These are the errors I get:

DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following "RE DAYCOUNT
INTEGER
". Expected tokens may include: "<SQL_variable_ declarations>". LINE
NUMBER=4. SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY) END WHILE RETURN DAYCOUNT END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT". Expected tokens may include: "SELECT


Thank you very much!!!!!

Ian
>
1) Put semi-colons at the end of each command.
2) If you are using the CLP, and have not specified otherwise, put a
bacslash at the end of each line.
3) The IN parameter STARTDATE should have the word "DATE" after it.
>
B.

Thanks Brian!!!

Still having a problem though. I am running it in the Control Center in
windows, since I'm running it on a windows server 2003 machine -
Version 8.1

Trying this...

------------------------------ Commands Entered
------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION


BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER;
SET DAYCOUNT = 0;

WHILE (STARTDATE <= ENDDATE) DO
IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1);
END IF;

SET (STARTDATE) = (STARTDATE + 1 DAY);
END WHILE;

RETURN DAYCOUNT;
END;
------------------------------------------------------------------------------
CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER


DETERMINISTIC NO EXTERNAL ACTION




BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "INTEGER" was found following "MIC
DECLARE
DAYCOUNT". Expected tokens may include: "END-OF-STATEMENT". LINE
NUMBER=10.
SQLSTATE=42601

SET DAYCOUNT = 0
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "DAYCOUNT" was found following "SET ".
Expected
tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

WHILE (STARTDATE <= ENDDATE) DO IF (DAYOFWEEK_ISO( STARTDATE) < 6) THEN
SET (COUNT) = (COUNT + 1)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "WHILE (STARTDATE <= ENDDATE)" was found
following "BEGIN-OF-STATEMENT". Expected tokens may include:
"<labeled_begin _atomic>". SQLSTATE=42601

END IF
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END IF".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SET (STARTDATE) = (STARTDATE + 1 DAY)
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "SET" was found following
"BEGIN-OF-STATEMENT".
Expected tokens may include: "SELECT". SQLSTATE=42601

END WHILE
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END
WHILE". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

RETURN DAYCOUNT
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"RETURN
DAYCOUNT". Expected tokens may include: "JOIN <joined_table>" .
SQLSTATE=42601

END
DB21034E The command was processed as an SQL statement because it was
not a
valid Command Line Processor command. During SQL processing it
returned:
SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END".
Expected tokens may include: "JOIN <joined_table>" . SQLSTATE=42601

SQL0104N An unexpected token "END-OF-STATEMENT" was found following
"END". Expected tokens may include: "JOIN <joined_table >

Any help would be appreciated!!!! !!!!!1

Thanks

Ian
>
Put a \ after each line. It looks like it is treating each line
separately.
>
Also, the loop may be a bad thing, for large dates. Instead, i'd look
at WEEK_ISO() and DAYS() for something like:
>
DAYS(EndDate) - DAYS(StartDate) - WEEK_ISO(EndDat e) +
WEEK_ISO(StartD ate)
>
if it is in the same year. For multiple years, a more complex solution
may be required.
>
And another note. Start is the opposite of Finish, Begin is the
opposite of End. Using Start and End is just plain wrong. :)
>
B.
Want to post what you cam up with? Perhaps someone else may find it
useful.

B.

It may not look too great, but it works! if the start and end dates are
on the same day, it counts 1 day...

CREATE FUNCTION WORKINGDAYS (STARTDATE DATE, ENDDATE DATE) RETURNS
INTEGER

DETERMINISTIC NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE DAYCOUNT INTEGER DEFAULT 0;--
DECLARE STARTDAYS INTEGER;--
DECLARE ENDDAYS INTEGER;--

SET STARTDAYS = DAYS(STARTDATE) ;--
SET ENDDAYS = DAYS(ENDDATE);--

IF (STARTDAYS <= ENDDAYS) THEN
WHILE (STARTDAYS <= ENDDAYS) DO
IF (DAYOFWEEK_ISO( DATE(STARTDAYS) ) < 6) THEN
IF ((STARTDAYS = ENDDAYS) AND (DAYCOUNT = 0)) THEN
SET DAYCOUNT = 1;--
END IF;--

IF (STARTDAYS < ENDDAYS) THEN
SET DAYCOUNT = DAYCOUNT + 1;--
END IF;--
END IF;--

SET STARTDAYS = STARTDAYS + 1;--
END WHILE;--
ELSE
SET DAYCOUNT = 0;--
END IF;--

RETURN DAYCOUNT;--
END

Hope you find it userful...

Ian
Thanx.

And it's not necessarily for me to find useful. :)

BTW, you may want to add "CONTAINS SQL", so the default of "READS SQL
DATA" is not used.

B.

Sep 1 '06 #9

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
2768
by: danny.myint | last post by:
I was under the assumption that javascript loads all the <head></head> elements before processing the <body> tag in Mozilla/Netscape/Firefox. It doesn't seem like it, with my problem. I have navigation that has onMouseOver and onMouseOut triggers, that use function calls in my external .js file. When I mouseOver a button in my navigation...
3
3772
by: Dave | last post by:
I have an Access 2K application that is distributed to about a dozen users (all with identical NT environments and identical Access versions, object libraries and service packs). I am using the VBA Replace function in a couple of queries to reformat some data. The application works flawlessly on 8 of the workstations, but on the other 4...
4
2048
by: sara | last post by:
I've gotten my first function to work - to build a sql string to load a list box. I call the function whenever I want the list box loaded 1. When a customer is chosen - opens the frmCust and shows the lstItems with all the customer's items (works great) 2. When an item is deleted - goes back to the frmCust and shows the lstItems and...
1
1650
by: Viktor Popov | last post by:
I have a javascript functions which are used for populating 2 select boxes. The first select box is used for continent names, the second for the countries in the contint. When I click on a continent, in the second select box, are shown the countries. Everything works well accept one thing. When I try to submit the selected items an error...
78
4917
by: Josiah Manson | last post by:
I found that I was repeating the same couple of lines over and over in a function and decided to split those lines into a nested function after copying one too many minor changes all over. The only problem is that my little helper function doesn't work! It claims that a variable doesn't exist. If I move the variable declaration, it finds the...
11
1971
by: The Frog | last post by:
Hi all, Maybe I am just missing something simple here, but I seem to have an issue with a callback function in A97 that is used to fill a Listbox with values. The first time the callback function is used (when the form opens) all runs well and everyone is happy. Then comes the problem - values are added to the recordset (ADO) that the...
6
1633
by: cppnow | last post by:
Hello. I have a strange conceptual problem I'm trying to think about. I would like to build a library that allows the user to do the following: 1) User defined types: The user defines their own types as needed for their particular application and registers them with the library. class ClassA {};
5
3370
by: Harinezumi | last post by:
Hi, while creating a hierarchy of classes I ran into a strange problem: it seems the compiler doesn't inherit virtual functions with the same name but different signature if only one of them is overridden in a derived class. This is the smallest piece of code I could see the problem appear: class UserType { public: UserType(float x) :...
0
7720
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8230
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6451
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
0
3739
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2240
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1337
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1056
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.