i want the o.transtype to come as B in the case of BUYL or S in the case of SELLL
select 'USPFEI' as [account_id], s.cusip as [security_id], 'CUSIP' as [security_id_type], s.sec_name as [description], s.ticker as [ticker], s.LIST_EXCH_CD as [exchange], convert(varchar, o.trade_date, 112) as [trade_date], convert(varchar,o.SETTLE_DATE, 112) as [settl_date], (select o.trans_type from ts_order o if o.trans_type = 'BUYL' then 'B' else 'S') as [txn_type], 'L' as [long_short_indicator],
a.exec_qty as [shares_par_value], a.exec_price as [price], 'USD' as [iso_currency], '' as [accrued_interest], a.commision_amt as [commission], a.fee_1_amt as [sec_fee], '' as [tax_amount], '' as [stamp_duty_tax], '' as [other_fees], a.net_prin_amt as [principal], a.exec_amt as [net_amount], a.order_id as [txn_number], '' as [cancel_flag], a.exec_broker as [broker]
from ts_order_alloc a
inner join ts_order o on a.order_id=o.order_id
inner join csm_security s on o.sec_id=s.sec_id
where o.status='ACCT' and(o.trade_date>='2008-02-09'
and o.trade_date<='2008-04-10') and a.acct_cd = 'bimcor'