At 10:43 AM 19-11-01 +0200, you wrote:

>Hi All,
>
> I have problems with the IB's trigger&SP language especially with IF
>THEN BEGIN END ELSE BEGIN END statements. I don't understand why we don't
>put semicolon after IF THEN ELSE Statements. And why I could not put any
>statement after IF THEN BEGIN END ELSE BEGIN END, if I try I have errors. So
>I need some "realy doin somethin" SP&Trigger examples, becouse the
>employee.gdb only have 4, 3 of it about same sentence. May be I'm wrong
>choosing FB for a project includes over 100 tables and over 150 SP's and
>much more trigger.

Why? just keep testing and fixing until you get the logic and syntax right. You have a logic bug in the embedded procedure; and you are misapplying the colon prefix to the variables in your calculation. There may be a logic bug in the main procedure, too, as I don't think you have all of the zero/null possibilities handled. Your code layout is very untidy - you are making it just too easy to commit logic errors.

Also, I prefer SELECT INTO when calling embedded procedures that return values, to avoid a bug that sometimes happens when you called the embedded procedure repeatedly (although I realise you are not doing repeat calls here).

You should always test numerics for NULL and handle it so that you never allow the possibility that your procedure will try to perform calculations with NULL operands.

I'll correct, below:

>This SP's aim to bring the value from another currency
>
>The DOVKART Table DOVKOD Varchar (5) , NAME varchar (20)
>USD, USDollar
>DEM, Deutche Mark
>TRL, Turkish Lira
>
>The DOVKUR table TARIH date,DOVKOD varchar(5),ALISKUR numeric(17,2),SATISKUR
>numeric (17,2)
>18.11.2001 DEM 760000 765000
>18.11.2001 USD 1550000 1560000
>18.11.2001 TRL 1 1
>
>
>I'm sending this values to KUR_DONUSTUR (18.11.2001,USD,DEM, 500,1 )
>
>the result must be this
>500 * (1560000/765000)
>500 * 2,039215686274509803921568627451
>1019,6078431372549019607843137255
>the result must be 1019,60 but I get Null
>
>how can I solve these problems????
>
>CREATE PROCEDURE KUR_DONUSTUR (
> TARIH DATE,
> FROMKUR VARCHAR(5),
> TOKUR VARCHAR(5),
> TUTAR NUMERIC(17,2),
> SATIS SMALLINT)
>RETURNS (
> KUR NUMERIC(17,2))
>AS
>declare variable FROMKURASMONEY NUMERIC (17,2);
>declare variable TOKURASMONEY NUMERIC (17,2);
>begin
>/* SATIS = 1 ÝSE SATIÞ KURU 0 ÝSE ALIÞ KURU */

select KUR from GETDOVIZKUR ( :FROMKUR , :TARIH , :SATIS ) into :FROMKURASMONEY;

>/* EXECUTE PROCEDURE getdovizkur :FROMKUR , :TARIH , :SATIS returning_values
>:FROMKURASMONEY; */

if ((FROMKURASMONEY <> 0) and (FROMKURASMONEY is not null)) then
BEGIN

>/* EXECUTE PROCEDURE getdovizkur :TOKUR , :TARIH , :SATIS returning_values
>:TOKURASMONEY; */

select KUR from GETDOVIZKUR ( :TOKUR , :TARIH , :SATIS ) into :TOKURASMONEY;
if ((TOKURASMONEY <> 0) and (TOKURASMONEY is not null)) then
begin

>/* KUR = TUTAR * (:FROMKURASMONEY / :TOKURASMONEY); */

KUR = TUTAR * (FROMKURASMONEY / TOKURASMONEY); /* notice no colons */

> END ELSE KUR = 0;
>END
>
>CREATE PROCEDURE GETDOVIZKUR (
> DOVKOD VARCHAR(5),
> TARIH DATE,
> TIP SMALLINT)
>RETURNS (
> KUR NUMERIC(17,2))
>AS
>begin
>/* if (TIP=0) then /* ALIÞ */ */

Here you have a logic bug. If TIP is not zero, then there is no value for KUR to pass back. Therefore KUR will return NULL.

> BEGIN
> SELECT ALISKUR
> FROM DOVKUR
> WHERE DOVKOD = :DOVKOD AND TARIH = :TARIH
> INTO :KUR;
> if (KUR IS NULL) then
> BEGIN
> KUR = 0;
> exception "doviz_girilmemis";
> END
> END ELSE /* SATIÞ */
> BEGIN
> SELECT SATISKUR
> FROM DOVKUR
> WHERE DOVKOD = :DOVKOD AND TARIH = :TARIH
> INTO :KUR;
> if (KUR IS NULL) then
> BEGIN
> KUR = 0;
> exception "doviz_girilmemis";
> END
> END
> suspend;
>end

regards,
Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________