Jump to content
Henry Olive

SQL Update Case When

Recommended Posts

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 by Henry Olive

Share this post


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

I assume this.

Share this post


Link to post

Thank you so much Stano

I added  'as MyResult'   after Case's  End

This time i get 'Token Unknown'  as   error

 

Share this post


Link to post

I apologise. Such a mistake


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

 

Share this post


Link to post

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

 

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post

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

Share this post


Link to post

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

 

Share this post


Link to post

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

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×