Avatar of tomasdlv
tomasdlv
Flag for United States of America asked on

Nested if or select statement

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'
SQL

Avatar of undefined
Last Comment
tomasdlv

8/22/2022 - Mon
folderol

case o.trans_type when  'BUYL' then 'B' else 'S' end as [txn_type],
tomasdlv

ASKER
it tells me it cannot return more than one value
ASKER CERTIFIED SOLUTION
ee_rlee

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
tomasdlv

ASKER
great thanks
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy