SQL Server: Calculations

Question

What is wrong with the following code

I am trying to calculate commissions based on an existing column and loading into a temp table, when I run the query, it gives me an error saying - Invalid column name 'Multiplier'.

SELECT *, Multiplier =
    Case PRODUCTNAME 
    When  'Voice' then 2.4
    When  'Cable' then 4.8
    Else 1
    END,
    Multiplier * COMP_RATE as Commission
  into #CommissionsFTMJul18
  FROM [DBPO_TRANS].[dbo].[CREDITS]

You need a nested query where you define `Multiplier` or recalculate the value of `Multiplier` for the column where you are trying to use that value. Duplicate of [Reference an alias elsewhere in the SELECT list](https://stackoverflow.com/q/11975749/1260204) — Igor, Jul 27 '18 at 14:23
I guess the case statement doesnt have a column name specified. That might be an error. Try giving a column name — Ranjana Ghimire, Jul 27 '18 at 14:23

score 0 · Accepted Answer · answered Jul 27 '18 at 14:30

0

You cannot define the name multiplier and use it in a calculation in the same statement. Just use two CASE statements:

SELECT *, Multiplier =
    Case PRODUCTNAME 
    When  'Voice' then 2.4
    When  'Cable' then 4.8
    Else 1
    END,
    Case PRODUCTNAME 
    When  'Voice' then 2.4
    When  'Cable' then 4.8
    Else 1
    END * COMP_RATE as Commission
  into #CommissionsFTMJul18
  FROM [DBPO_TRANS].[dbo].[CREDITS]

answered Jul 27 '18 at 14:30

Wolfgang Kais

4,010
2
10
17

Thanks, It works... – Abdul Qavi Jul 27 '18 at 14:38

SQL Server: Calculations

1 Answers1