title: SQL: Add a new column based on CASE expression and looking up values from another table
body: <p>I am trying to add a new column called Multiplier to an existing table called Trades. The row values of this column will depend on another column on the Trades table called Type. If the Type is anything other than "Equity", "Corp" or "Option, then the value needs to be looked up from another table called ContractSize. Lastly, I want the data type of the Multiplier column to be decimal (7,3). The code I had was:</p>
  
  <pre><code>ALTER TABLE Portfolio.Trades
  ADD Multiplier decimal(7,3) AS
  (
  CASE 
   WHEN Type = 'Equity' Then 1
   WHEN Type = 'Corp' Then 0.1
   WHEN Type = 'Option' Then 100
  ELSE
   (SELECT ContractSize FROM Portfolio.ContractSize CS
   JOIN Portfolio.Trades T
   ON T.Identifier = CS.ContractSize)
  )
  </code></pre>
  
  <p>I am getting two errors on this code:</p>
  
  <p>Msg 156, Level 15, State 1, Line 2
  Incorrect syntax near the keyword 'AS'.<br>
  Msg 102, Level 15, State 1, Line 12
  Incorrect syntax near ')'.  </p>
  
  <p>Some guidance is much appreciated. </p>