I am new at SQL and am trying to design a calculated field that is dependent on the value of another field
I have a "total_Comm field" who's calculated value is dependent on the value of a "VAT field" either being yes or no and "Comission_paid field" so that if the value is yes then the calculated field will be "Comission_paid field" * 1.175 or if no then "Comission_paid field" * 1
I have tried the alter table statement below but keep getting a syntax error. Also I am not sure that the logic of this is right or if there is a better way to do this
ALTER TABLE [Actual Recuring Comm]
ADD Total_Comm AS
WHEN 0 THEN 0
IF "VAT = Yes"
THEN (Comission_paid * 1.175)
IF "VAT = No"
THEN Commission_paid * 1
Any help or pointers would be appreciated