Question about IIF in SQL query
 

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?

Question about IIF in SQL query
 

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?

Question about IIF in SQL query
 

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".

Question about IIF in SQL query
 

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".

Question about IIF in SQL query
 

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?

Question about IIF in SQL query
 

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.

Question about IIF in SQL query
 

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.