Select column based on another column value-CodePudding

I have a query that I would like to select a different column based on a value billback_id. If billback_id = 3 then I want to select bhh.ctv otherwise I want bsih.total. Is this possible in a join?

select
bsih.invoice_amount, 
case when bhh.billback_id = 3 then bhh.ctv else bsih.total end as bsih.total
from [movement].[dbo].[billback_header_history] as bhh
left join
(select invoice_number, invoice_extension,store_number, sum(ctv) as total
from [movement].[dbo].[bsih]
where ctv <> 0
group by invoice_number, invoice_extension, store_number
) as bsih
on bhh.invoice_number = bsih.invoice_number
where last_movement_update between '2022/01/08' and '2022/01/14'

error

Msg 156, Level 15, State 1, Line 111 Incorrect syntax near the keyword 'if'. Msg 156, Level 15, State 1, Line 111 Incorrect syntax near the keyword 'then'. Msg 156, Level 15, State 1, Line 119 Incorrect syntax near the keyword 'as'.

CodePudding user response：

In SQL, you can use case (instead of if) in the select.

The statement is:

case when {condition1} then {result1} when {condition2} then {result2} else {result3} end.

So, we could have:


select
    bsih.invoice_amount, 
    case when bhh.billback_id = 3 then bhh.ctv
        else bhih.total end as col2
from [movement].[dbo].[billback_header_history] as bhh
left join (
    select invoice_number, invoice_extension,store_number, sum(ctv) as total
    from [movement].[dbo].[bsih]
    where ctv <> 0
    group by invoice_number, invoice_extension, store_number
) as bsih on bhh.invoice_number = bsih.invoice_number
where last_movement_update between '2022/01/08' and '2022/01/14'