Walter,

are you sure that STICHTAG2 (which seems to be the last day in month) is
what you're really looking for?

Are you sure that with

> WHERE :STICHTAG2 >= (SELECT MAX(EA.EINTRITTSDATUM) FROM ...

there's not "Eintrittsdatum" in the 2nd view which is AFTER STICHTAG2
(thus returning no more records)?

You wrote, that before using params you used TODAY.
For which variable did you use today?

What happens if you execute (and assign the params of) this procedure
directly in any DB-tool?

Have a look at your records and decide which records you think should be
returned and compare the fields with your SQL.
Sometimes it helps to test with single SQL statements (enter all your
WHERE -parts directly and see if they really return what you think
they'll do).


Luc.




Walter Neumann schrieb:

>
> Thank you Helen for the reply. Here the answers:
>
> Am Freitag, 7. März 2003 23:39 schrieb Helen Borrie:
> > At 05:02 PM 7/03/2003 +0100, you wrote:
> > >Hi,
> > >I wrote a SP, which worked fine. Then I changed it, and now I recieve
> > > always ANZAHL=0. The 1. SP had no input parameters. I used always 'TODAY'
> > > for comparison. Here is the new SP:
> > >
> > >ALTER PROCEDURE "MITARB_COUNT"
> > >(
> > > "STICHTAG2" TIMESTAMP,
> > > "STICHTAG1" TIMESTAMP
> > >)
> > >RETURNS
> > >(
> > > "ANZAHL" INTEGER
> > >)
> > >AS
> > >DECLARE VARIABLE MAID INT;
> > >BEGIN
> > > ANZAHL = 0;
> > > FOR SELECT MA.PERSONENIDINT FROM MITARBVIEW MA
> > > WHERE :STICHTAG2 >= (SELECT MAX(EA.EINTRITTSDATUM) FROM
> > > MITARBEINAUSVIEW
> > >EA
> > > WHERE ((EA.MITARBID = MA.PERSONENIDINT)
> > > AND ((EA.AUSTRITTSDATUM IS NULL) OR (EA.AUSTRITTSDATUM >=
> > >
> > >:STICHTAG1))))
> > >
> > > INTO :MAID
> > > DO
> > > BEGIN
> > > ANZAHL = ANZAHL + 1;
> > > END
> > > SUSPEND;
> > >END
> > >
> > >Thank you for your advices.
> >
> > Try shutting down the database server and then restarting it.
> > Next, test your procedure again and see whether it now works as expected.
>
> The database server is daily restartet. I work for developing local. The new
> version is executed, because I can see the input parameters in Kylix. The old
> version without input parameters works properly. So it must have todo
> somthing with the input parameters.
> >
> > If it still doesn't work as expected, then show us an example of how you
> > are calling this new version of the procedure?
>
> The SP is executet by Borlands Kylix/Delphi dbexpress driver:
> MACountSP.ParamByName('STICHTAG1').AsDateTime:=EncodeDate(Jahr, Monat, 1);
> MACountSP.ParamByName('STICHTAG2').AsDateTime:=EncodeDate(Jahr, Monat,
> MonthDays[IsLeapYear(Jahr), Monat]);
> MACountSP.ExecProc;
> SelRec:=MACountSP.ParamByName('ANZAHL').AsInteger;
> >
> > Because date types are involved, it's also necessary to tell us which
> > dialect you are using.
>
> I use FB 1.0.0 dialect 3.
> >
> Thank you for all advices.
>
> Walter.
>
>
> 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/