By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

function workingdays problem

P: n/a
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
Share this Question
Share on Google+
8 Replies


P: n/a

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

P: n/a

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

P: n/a
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(EndDate) +
WEEK_ISO(StartDate)

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

P: n/a
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(EndDate) +
WEEK_ISO(StartDate)

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

P: n/a
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(EndDate) +
WEEK_ISO(StartDate)

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

P: n/a

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(EndDate) +
WEEK_ISO(StartDate)
>
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

P: n/a

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(EndDate) +
WEEK_ISO(StartDate)

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

P: n/a

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(EndDate) +
WEEK_ISO(StartDate)
>
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 discussion thread is closed

Replies have been disabled for this discussion.