Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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". |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Web Query question | Excel Discussion (Misc queries) | |||
One Last Query Question | Excel Discussion (Misc queries) | |||
Web Query Question | New Users to Excel | |||
MS Query question | Excel Worksheet Functions | |||
Web Query Question | Excel Discussion (Misc queries) |