473,785 Members | 2,819 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 3942

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
2781
.js file not loading in time for function call, problem only in Netscape/Mozilla/Firefox
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 quickly enough to catch it before the rest of the body loads, it returns a function "not defined"...
Javascript
3
3783
Replace function - odd problem
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 throws a "Unrecognized function Replace" error when the queries in question are encountered. I have...
Microsoft Access / VBA
4
2057
First Function - One Problem!
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 the deleted item is gone (works fine) 3. When an item is changed - goes back to the frmCust and...
Microsoft Access / VBA
1
1659
Asp.NET javascript function postback problem
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 occured: "Object doesn't support this property or method!" Line 124. On this line is the postIt()...
C# / C Sharp
78
4976
Nested function scope problem
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 variable, but can't change it. Declaring the variable global in the nested function doesn't work...
Python
11
1985
Callback Function requery problem in A97
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 callback function uses to populate the listbox. After the new values are added to the recordset, the...
Microsoft Access / VBA
6
1647
Conceptual function type problem
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 {};
C / C++
5
3387
Virtual function signature problem
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) : x(x) {} float x; };
C / C++
0
9645
marktang
What is ONU?
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
General
0
9480
Changing the language in Windows 10
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
Windows Server
0
10324
Oralloy
Problem With Comparison Operator <=> in G++
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
C / C++
0
10147
jinu1996
Maximizing Business Potential: The Nexus of Website Design and Digital Marketing
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 captivates audiences and drives business growth. The Art of Business Website Design Your website is...
Online Marketing
1
7499
isladogs
Access Europe - Using VBA to create a class based on a table - Wed 1 May
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
Microsoft Access / VBA
0
5380
Trying to create a lan-to-lan vpn between two differents networks
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 the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
Networking - Hardware / Configuration
0
5511
Windows Forms - .Net 8.0
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
Visual Basic .NET
2
3645
muto222
How to add payments to a PHP MySQL app.
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
PHP
3
2879
bsmnconsultancy
Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...
General

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.

BYTES.COM © 2024
About Bytes
Terms Of Use
Privacy Policy
Sitemap
Advertise on Bytes
How to Post and Respond on Bytes
How to Promote and Link on Bytes
How to increase your Ranking on Bytes
Become a Recognized Expert on Bytes
Feedback Welcomed! Contact Us