SQL Update Case When

[Henry Olive 5]

Interbase 2007

 

CREATE PROCEDURE EMPTAXBASE 
( TMONTH SMALLINT)
AS
declare variable EMPPORTION NUMERIC(12, 5);
begin
  SELECT EMPPORTION FROM COMPANY
   INTO :EMPPORTION;

 

  UPDATE EMPLOYEE SET TAXBASE=
    Case 
      When SHAREHOLDER='Y' then (BRUTSALARY * :EMPPORTION /100) * :TMONTH

   End                             
end

 

I'm getting 'Token Unknown end' error  ( which is the LAST end, the other is End )

what am i doing wrong ?

Thank You

Edited February 14, 2021 by Henry Olive

[Stano 143]

    Case 
      When SHAREHOLDER='Y' then (BRUTSALARY * :EMPPORTION /100) * :TMONTH AS MYRESULT
   End     

I assume this.

[Henry Olive 5]

Thank you so much Stano

I added  'as MyResult'   after Case's  End

This time i get 'Token Unknown'  as   error

 

[Stano 143]

I apologise. Such a mistake

    Case 
      When SHAREHOLDER='Y' then (BRUTSALARY * :EMPPORTION /100) * :TMONTH
   End AS MYRESULT

 

[Henry Olive 5]

Thank you so much Stano

I noticed that you made a typo mistake

I added *as* after End (Case's End)

like :  as MYRESULT End

error is Token Unknown  as

 

[Stano 143]

Since I don't do it, I can only guess. I only work with Firebird. See help. It should be behind the penultimate end ";" ?
I have nothing more to say.

[Henry Olive 5]

I created a simple S.Proc in EMPLOYEE  Database

If someone has 5 minutes time may be he can try to

create below s.proc.

This proc also gives  error 

'Token unknown - line 12, char -1 end'

CREATE PROCEDURE TEST
AS
  DECLARE VARIABLE MYCOUNTRY VARCHAR(40);
begin
  SELECT COUNTRY FROM COUNTRY WHERE COUNTRY='Fiji'
  INTO :MYCOUNTRY; 

  UPDATE CUSTOMER SET COUNTRY =
  Case
     When COUNTRY='USA' then :MYCOUNTRY
     When COUNTRY <> 'USA' then COUNTRY
  End
end

[Stano 143]

I looked in the help. The update part is not finished with ";"

[Henry Olive 5]

Stano, Thank You SO SO MUCH for your help, i solved the problem

There were 2 problems

1=Case should have been between Parentheses

2=At the end of  statement  there should have been   ;

(Case when aa then bb else cc end) ;

 

[Serge_G 87]

Hi,

 

This one

UPDATE CUSTOMER SET COUNTRY =
  Case
     When COUNTRY='USA' then :MYCOUNTRY
     When COUNTRY <> 'USA' then COUNTRY
  End

should be replaced by

UPDATE CUSTOMER SET COUNTRY='USA' WHERE COUNTRY=:MYCOUNTRY;

 

And this

UPDATE EMPLOYEE SET TAXBASE=
    Case 
      When SHAREHOLDER='Y' then (BRUTSALARY * :EMPPORTION /100) * :TMONTH

   End                             
end 

by

UPDATE EMPLOYEE E SET E.TAXBASE=E.BRUTSALARY*:EMPORTION/100)*:TMONTH WHERE E.SHAREHOLDER='Y';