Help with CASE & COALESCE

[katbear]

Hi,

Getting errors on:

SELECT 
  ab.ClosingBalance,
  CASE
    WHEN v1.Broker = 'Smith Barney'
      THEN NULL
    ELSE
      COALESCE(v1.Balance1, v2.Balance2) AS BegBalance
   END
FROM myView1 v1
LEFT OUTER JOIN myView2 v2
ON  v1.Broker = v2.Broker

The error is:

Msg 156, Level 15, State 1, Procedure myProc, Line 44
Incorrect syntax near the keyword 'AS'.

Doesn't seem to like the "AS", but I need to do this.

Please help.

Thanks

 

[jsql12]

Hi,

It's not that it doesn't like the word "AS". Try putting single quotes around BegBalance like this 'BegBalance'.

GOOD LUCK!

 

[bborissov]

Name of the field must be after the END of the CASE

SELECT
  ab.ClosingBalance,
  CASE
    WHEN v1.Broker = 'Smith Barney'
      THEN NULL
    ELSE
      COALESCE(v1.Balance1, v2.Balance2)
   END [COLOR=red]AS BegBalance[/color]
FROM myView1 v1
LEFT OUTER JOIN myView2 v2
ON  v1.Broker = v2.Broker

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[katbear]

Thanks, putting it after the END worked