Bill Katelis wrote:

> 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

Hi
Sortorder is an integer yes.

Advance should be an varchar in proc CHECK_WORKINGDAYSTEMP_PIETA.
Changing it however, does not make a difference. I checked that all my
types correlate between procedures and tables. The only types that does
not precisely correlate is "workingday" from "select workingday from
workingdays" and "temp" variable. "workingday" is a varchar(1) domain
called boolean with a "0" or "1" check constraint where is temp is just
a plain varchar(1)

Groete
Gerhardus

>
> 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
>>