Question about IIF in SQL query

  • Thread starter Thread starter dchow
  • Start date Start date
D

dchow

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)
 
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.
 
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)
 
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.
 
If it is SQL server, that should support DECODE. Sybase does.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Don't know what was wrong. It still returned syntax error when I used
DECODE.

I was doing
SELECT ....,
DECODE(DOCTYPE,"RET",-DOCAMT,DOCAMT),
 
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
 
It worked perfectly. Thanks Robin.


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
Click to expand...
 
Back
Top