Gerhadus -

Is sortorder defined as an integer in table workingdays ?

In proc CHECK_WORKINGDAYSTEMP_PIETA advance is an integer whereas in
CHECK_WORKINGDAYSTEMP it is a varchar. Is this correct ?

bill

Gerhardus Geldenhuis wrote:

>Hi
>I have a stored procedure with the following code:
>SET TERM ^! ;
>CREATE PROCEDURE CHECK_WORKINGDAYSTEMP (IN_BEGIN Date) returns (OUT_DATE
>Date) AS
>DECLARE VARIABLE Advance varChar(1);
>declare variable Temp varchar(1);
>
>declare variable Counts Integer;
>
>begin
> Counts = 0;
> Advance = '1';
>
> while (Advance = '1') do
> begin
> Counts = Counts + 1;
> select workingday from workingdays
> where sortorder = (select result from dayofweek_select(:In_Begin))
> into Temp;
>
> if (Temp = '0') then
> In_Begin = :In_Begin + 1;
> else
> Advance = '0';
>
> if (Counts = 5) then
> begin
> Advance='0';
> end
> end
>
> Out_Date = In_Begin;
>
>end
>
>^!
>SET TERM ; ^!
>
>The problem is
>
> select workingday from workingdays
> where sortorder = (select result from dayofweek_select(:In_Begin))
> into Temp;
>
>always seems to return the same value which is '0' causing the procedure
>to go into an endless loop. That why I have the "if (Counts = 5) then"
>piece of code.
>
>dayofweek_select is a normal executable procedure converted to a select
>procedure by inserting a suspend. Here is the code just in case:
>
>SET TERM ^! ;
>CREATE PROCEDURE DAYOFWEEK_SELECT (ADATE Date) returns (RESULT Integer) AS
>declare variable Elapsed Integer;
>begin
> Elapsed = ADate-cast(('1.1.96')as date);
> execute procedure Modulus(Elapsed, 7) returning_values Result;
>
> if (Result <= 5) then
> Result = Result + 2;
> else
> Result = Result - 5;
> Suspend;
>end
>
>^!
>SET TERM ; ^!
>
>
>What is interesting is that if you split the following statement into
>two part then it works fine:
>
> select workingday from workingdays
> where sortorder = (select result from dayofweek_select(:In_Begin))
> into Temp;
>
>Here is the procedure with the above statement split into two:
>
>SET TERM ^! ;
>CREATE PROCEDURE CHECK_WORKINGDAYSTEMP_PIETA (IN_DATE Date) returns
>(OUT_DATE Date) AS
>DECLARE VARIABLE dayofweek integer;
>DECLARE VARIABLE isworkingday integer;
>DECLARE VARIABLE advance integer;
>
>begin
> advance = '1';
>
> while (Advance = '1') do
> begin
> select result
> from dayofweek_select(:IN_DATE)
> into :dayofweek;
>
> select workingday from workingdays
> where sortorder = :dayofweek
> into :isworkingday;
>
> if (isworkingday = '1') then begin
> OUT_DATE = IN_DATE;
> advance = '0';
> end
> else
> IN_DATE = IN_DATE + 1;
> end
>end
>
>^!
>SET TERM ; ^!
>
>
>I have written all the variables to a tempvalue and the problem is
>deffinitely the select statement but I dont know why.
>
>Groete
>Gerhardus
>
>
>To unsubscribe from this group, send an email to:
>ib-support-unsubscribe@egroups.com
>
>
>
>Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>