I just had a look at the sql books online (you can get it off msdn
somewhere) and it doesn't appear to support IIF outside analysis services or
Decode.
I think you might be looking for a CASE statement.
I can't test this without recreating your db but the format looks something
like
SELECT DOCNUM AS Document,
DOCDATE AS DocDate,
'AMOUNT' =
CASE
WHEN DOCTYPE = 'RET' THEN
-DOCAMT
ELSE
DOCAMT
END
FROM TableName
Give it a try and let's us know if it works.
Here's a slightly more complex example I just had a look at from one of my
SQL views that might help. This gets the left area of ItemName up to a star
character.
SELECT 'ShortName' =
CASE
WHEN CHARINDEX('*',template_info.ItemName)0 THEN
LEFT(template_info.ItemName,
CHARINDEX('*',template_info.ItemName)-1)
ELSE
template_info.ItemName
END,
corp_info.FullName
FROM corp_info
Robin Hammond
www.enhanceddatasystems.com
"dchow" wrote in message
...
Don't know what was wrong. It still returned syntax error when I used
DECODE.
I was doing
SELECT ....,
DECODE(DOCTYPE,"RET",-DOCAMT,DOCAMT),
On Sat, 7 Feb 2004 00:07:43 -0000, "Bob Phillips"
wrote:
If it is SQL server, that should support DECODE. Sybase does.