IF Exists within Case statements 1

[iluvwitter]

Hi,

I am trying to use multiple case statements based on the manager code selected. Depending on the manager code, I need to check the holdings and trade tables. If the symbol exists in either the holdings or trades table, default that strategy, else default "NEW_STRAT". Im not sure what the best way is to go about this but this doesn't work.


CASE WHEN @MANAGER = '123'
THEN CASE
IF EXISTS( select strategy1 from ec_hldlot where portcd = 'ABC'
AND SEC_SYM = @SYMBOL)
THEN CASE
IF EXISTS( select strategy1 from ec_tradelot AS trade
where portcd = 'ABC
AND Symbol = @SYMBOL
ELSE 'NEW_STRAT'

CASE WHEN @MANAGER = '456'
THEN CASE
IF EXISTS (SELECT STRATEGY1......

 

[PHV]

I'd use COALESCE:

CASE @MANAGER
WHEN '123' THEN COALESCE((select strategy1 from ec_hldlot where portcd='ABC' and sec_sym=@SYMBOL),(select strategy1 from ec_tradelot where portcd='ABC' and symbol=@SYMBOL),'NEW_STRAT')
WHEN '456' THEN COALESCE(......)
ELSE 'bad_manager'
END

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886