Board index » delphi » SQL Problem...

SQL Problem...

Hi, Can anybody help me, what ma i doing wrong:
Thanks.
SQL=
{
set term ; ~
create procedure CALCOLA_PUNTI
(IN_FK_FUNZIONARIO INTEGER)
RETURNS (OUT_PUNTI INTEGER)
AS
 DECLARE VARIABLE VAL_FORM INTEGER;
 DECLARE VARIABLE PUNTI_FORM INTEGER;
 DECLARE VARIABLE PER_OGNI_FORM INTEGER;
 DECLARE VARIABLE SUM_FORM INTEGER;
BEGIN
 SELECT VALORE, PUNTI, PER_OGNI FROM SPECIFICHE
    WHERE CODICE LIKE 'FROM'
   INTO :VAL_FORM, :PUNTI_FORM, :PER_OGNI_FORM;
 SELECT SUM(FORMAZIONE) FROM MONITORAGGI
   WHERE FK_FUNZIONARIO = :IN_FK_FUNZIONARIO
  INTO :SUM_FORM;
 OUT_PUNTI=0;
 WHILE(:SUM_FORM>=:VAL_FROM) DO
 BEGIN
  OUT_PUNTI=OUT_PUNTI+PUNTI_FORM;
  SUM_FORM=SUM_FORM-VAL_FORM;
 END
END
~
set term ~ ;
Quote
}

 

Re:SQL Problem...


Please explain your problem and include any error messages you receive.

--
Bill
(TeamB cannot answer questions received via email)

Re:SQL Problem...


Please give the error message when you ask such a question.
One thing I see now is that you type VAL_FROM in the while-clause
instead of VAL_FORM.
The colons in the while-clause are not necessary, but probably they
don't do any harm either.

HTH,
Gerrit

Oleg Lozinskij schreef:

Quote

> Hi, Can anybody help me, what ma i doing wrong:
> Thanks.
> SQL=
> {
> set term ; ~
> create procedure CALCOLA_PUNTI
> (IN_FK_FUNZIONARIO INTEGER)
> RETURNS (OUT_PUNTI INTEGER)
> AS
>  DECLARE VARIABLE VAL_FORM INTEGER;
>  DECLARE VARIABLE PUNTI_FORM INTEGER;
>  DECLARE VARIABLE PER_OGNI_FORM INTEGER;
>  DECLARE VARIABLE SUM_FORM INTEGER;
> BEGIN
>  SELECT VALORE, PUNTI, PER_OGNI FROM SPECIFICHE
>     WHERE CODICE LIKE 'FROM'
>    INTO :VAL_FORM, :PUNTI_FORM, :PER_OGNI_FORM;
>  SELECT SUM(FORMAZIONE) FROM MONITORAGGI
>    WHERE FK_FUNZIONARIO = :IN_FK_FUNZIONARIO
>   INTO :SUM_FORM;
>  OUT_PUNTI=0;
>  WHILE(:SUM_FORM>=:VAL_FROM) DO
>  BEGIN
>   OUT_PUNTI=OUT_PUNTI+PUNTI_FORM;
>   SUM_FORM=SUM_FORM-VAL_FORM;
>  END
> END
> ~
> set term ~ ;
> }

Re:SQL Problem...


Error message/problem description would help.
Obvious mistakes are:

1. LIKE needs '%' included with search word ('%FROM', 'FROM%' or '%FROM%')

2. I'm quite sure that you gonna get multiple records in
SELECT VALORE, PUNTI, PER_OGNI FROM SPECIFICHE
WHERE CODICE LIKE 'FROM'
IB expects one only. Make sure that this statement returns single record or
build 'for' loop there.

3.default terminator is ;. It means you need to start with set term ~; and
end with set term ;~

4.VAL_FROM mistyped (VAL_FORM)

Igor

Quote
"Oleg Lozinskij" <o_lozins...@yahoo.com> wrote in message

news:3b94c748_1@dnews...
Quote
> Hi, Can anybody help me, what ma i doing wrong:
> Thanks.
> SQL=
> {
> set term ; ~
> create procedure CALCOLA_PUNTI
> (IN_FK_FUNZIONARIO INTEGER)
> RETURNS (OUT_PUNTI INTEGER)
> AS
>  DECLARE VARIABLE VAL_FORM INTEGER;
>  DECLARE VARIABLE PUNTI_FORM INTEGER;
>  DECLARE VARIABLE PER_OGNI_FORM INTEGER;
>  DECLARE VARIABLE SUM_FORM INTEGER;
> BEGIN
>  SELECT VALORE, PUNTI, PER_OGNI FROM SPECIFICHE
>     WHERE CODICE LIKE 'FROM'
>    INTO :VAL_FORM, :PUNTI_FORM, :PER_OGNI_FORM;
>  SELECT SUM(FORMAZIONE) FROM MONITORAGGI
>    WHERE FK_FUNZIONARIO = :IN_FK_FUNZIONARIO
>   INTO :SUM_FORM;
>  OUT_PUNTI=0;
>  WHILE(:SUM_FORM>=:VAL_FROM) DO
>  BEGIN
>   OUT_PUNTI=OUT_PUNTI+PUNTI_FORM;
>   SUM_FORM=SUM_FORM-VAL_FORM;
>  END
> END
> ~
> set term ~ ;
> }

Re:SQL Problem...


Wow! Yes you are right, it was my mistake, and i received error "Column
unknown."
Thanks.
Quote
"Gerrit Stegehuis" <Stegeh...@cbs.knaw.nl> wrote in message

news:3B94E359.A56448B7@cbs.knaw.nl...
Quote
> Please give the error message when you ask such a question.
> One thing I see now is that you type VAL_FROM in the while-clause
> instead of VAL_FORM.
> The colons in the while-clause are not necessary, but probably they
> don't do any harm either.

> HTH,
> Gerrit

> Oleg Lozinskij schreef:

> > Hi, Can anybody help me, what ma i doing wrong:
> > Thanks.
> > SQL=
> > {
> > set term ; ~
> > create procedure CALCOLA_PUNTI
> > (IN_FK_FUNZIONARIO INTEGER)
> > RETURNS (OUT_PUNTI INTEGER)
> > AS
> >  DECLARE VARIABLE VAL_FORM INTEGER;
> >  DECLARE VARIABLE PUNTI_FORM INTEGER;
> >  DECLARE VARIABLE PER_OGNI_FORM INTEGER;
> >  DECLARE VARIABLE SUM_FORM INTEGER;
> > BEGIN
> >  SELECT VALORE, PUNTI, PER_OGNI FROM SPECIFICHE
> >     WHERE CODICE LIKE 'FROM'
> >    INTO :VAL_FORM, :PUNTI_FORM, :PER_OGNI_FORM;
> >  SELECT SUM(FORMAZIONE) FROM MONITORAGGI
> >    WHERE FK_FUNZIONARIO = :IN_FK_FUNZIONARIO
> >   INTO :SUM_FORM;
> >  OUT_PUNTI=0;
> >  WHILE(:SUM_FORM>=:VAL_FROM) DO
> >  BEGIN
> >   OUT_PUNTI=OUT_PUNTI+PUNTI_FORM;
> >   SUM_FORM=SUM_FORM-VAL_FORM;
> >  END
> > END
> > ~
> > set term ~ ;
> > }

Other Threads