I was trying to send a SQL query using ODBC to fill my worksheet with
some invoice information. In the Amount field I want it to be in
negative if the doc type is RETURN. So I did:

Select DOCNUM as Document,
DOCDATE as DocDate,
IIF(DOCTYPE="RET", -DOCAMT, DOCAMT) as Amount
From TableName

I was complaining about SQL syntax error. If I removed the IIF line
it worked fine. Does SQL support IIF?

Not AFAIK. Why don't you return DOCTYPE and DOCAMT and test DOCTYPE in Excel
and negate DOCAMT if ="RET".

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dchow" wrote in message
...
I was trying to send a SQL query using ODBC to fill my worksheet with
some invoice information. In the Amount field I want it to be in
negative if the doc type is RETURN. So I did:

Select DOCNUM as Document,
DOCDATE as DocDate,
IIF(DOCTYPE="RET", -DOCAMT, DOCAMT) as Amount
From TableName

I was complaining about SQL syntax error. If I removed the IIF line
it worked fine. Does SQL support IIF?

I can't do that because what I was doing was to get a total amount for
each customer. If I did what you suggested I would easily fill all
65536 rows. If there is no way I have to do it in 2 queries, one for
iinvoice and one for return.
I thought if this could be done in Access why not Excel.

On Fri, 6 Feb 2004 20:29:37 -0000, "Bob Phillips"
wrote:

Not AFAIK. Why don't you return DOCTYPE and DOCAMT and test DOCTYPE in Excel
and negate DOCAMT if ="RET".

Okay, the other poster pointed out though that even if you can't use IIF,
there is always another way, DECODe for instance.

Is it Access you are querying?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"dchow" wrote in message
...
I can't do that because what I was doing was to get a total amount for
each customer. If I did what you suggested I would easily fill all
65536 rows. If there is no way I have to do it in 2 queries, one for
iinvoice and one for return.
I thought if this could be done in Access why not Excel.

On Fri, 6 Feb 2004 20:29:37 -0000, "Bob Phillips"
wrote:

Not AFAIK. Why don't you return DOCTYPE and DOCAMT and test DOCTYPE in
Excel
and negate DOCAMT if ="RET".

I will try with DECOD.
No,it is not an Access database. It is a SQL database. I just tried to
access it with ACCESS queries. IIF worked with ACCESS but not Excel.

On Fri, 6 Feb 2004 23:39:58 -0000, "Bob Phillips"
wrote:

Okay, the other poster pointed out though that even if you can't use IIF,
there is always another way, DECODe for instance.

Is it Access you are querying?

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.

It worked perfectly. Thanks Robin.


On Sat, 7 Feb 2004 20:52:05 +0800, "Robin Hammond"
wrote:

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.