SQL syntax error 2

[sangrg]

following SQL gives err msg 'incorrect syntax near '='
What should be the correct syntax? Thanks.
Using SQL Svr 2000

SELECT balance.item_id, balance.gl_year, balance.gl_period, balance.fund_id, balance.fund_company, balance.account_description,
balance.account_type,
balance.summary_amt = Case balance.account_type
WHEN balance.account_type ='Assets' THEN balance.summary_amt
WHEN balance.account_type ='Liabilities' THEN balance.summary_amt * -1
END,

FROM balance

 

[SQLDenis]

I would do it like this

SELECT balance.item_id, balance.gl_year, balance.gl_period, balance.fund_id, balance.fund_company, balance.account_description,
balance.account_type,
Case balance.account_type
WHEN 'Assets' THEN balance.summary_amt
WHEN 'Liabilities' THEN balance.summary_amt * -1
END summary_amt

FROM balance

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

The problem is with your case syntax.

[tt][blue]
Case balance.account_type
WHEN 'Assets' THEN balance.summary_amt
WHEN 'Liabilities' THEN balance.summary_amt * -1
END
[/blue][/tt]

OR...

[tt][blue]
Case WHEN balance.account_type = 'Assets' THEN balance.summary_amt
WHEN balance.account_type ='Liabilities' THEN balance.summary_amt * -1
END
[/blue][/tt]

SELECT balance.item_id, 
       balance.gl_year, 
       balance.gl_period, 
       balance.fund_id, 
       balance.fund_company, 
       balance.account_description, 
       balance.account_type,
       Case balance.account_type
            WHEN 'Assets' THEN balance.summary_amt
            WHEN 'Liabilities' THEN balance.summary_amt * -1
            END As summary_amt
FROM balance

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom