Help Finding SQL Server equivalent of IIF

Help Finding SQL Server equivalent of IIF

Post by Siv » Tue, 13 May 2003 07:26:06



Hi,
I am converting some Access queries to work under SQL Server and have hit a problem with an IIF inside a SUM.

The problem is that the sum needs to be positive if another field contains a 0 and negative if that field contains a 1.  The original Sum command from the Access query went along the lines of:

Select Sum(iif(MyTable.FieldName=1,(-1*MyTable.AnotherField),MyTable.AnotherField)) AS SumFieldName FROM etc ...

The IIF inside the sum would sum in  (-1 * AnotherField) if FieldName is 1, or would just sum AnotherField if FieldName was 0.

I have tried replacing the IIF with an IF statement and I keep getting syntax errors.  Does anyone know how to achieve the same thing?

Any help appreciated.

--
Siv
Martley, Near Worcester, UK.

 
 
 

Help Finding SQL Server equivalent of IIF

Post by John Ellio » Tue, 13 May 2003 08:06:25


Hi Siv,

Try something like this:

SELECT SUM(CASE WHEN MyTable.FieldName = 1 THEN (-1 * MyTable.AnotherField)
ELSE MyTable.AnotherField END) AS SumFieldName FROM etc..

John.


Hi,
I am converting some Access queries to work under SQL Server and have hit a
problem with an IIF inside a SUM.

The problem is that the sum needs to be positive if another field contains a
0 and negative if that field contains a 1.  The original Sum command from
the Access query went along the lines of:

Select
Sum(iif(MyTable.FieldName=1,(-1*MyTable.AnotherField),MyTable.AnotherField))
AS SumFieldName FROM etc ...

The IIF inside the sum would sum in  (-1 * AnotherField) if FieldName is 1,
or would just sum AnotherField if FieldName was 0.

I have tried replacing the IIF with an IF statement and I keep getting
syntax errors.  Does anyone know how to achieve the same thing?

Any help appreciated.

--
Siv
Martley, Near Worcester, UK.

 
 
 

Help Finding SQL Server equivalent of IIF

Post by Siv » Tue, 13 May 2003 08:16:13


Many thanks John,

I had looked at CASE and thought it would suffer the same problem as the IF.
Will give it a try and report back to the group.

Siv


Quote:> Hi Siv,

> Try something like this:

> SELECT SUM(CASE WHEN MyTable.FieldName = 1 THEN (-1 *

MyTable.AnotherField)
> ELSE MyTable.AnotherField END) AS SumFieldName FROM etc..

> John.



> Hi,
> I am converting some Access queries to work under SQL Server and have hit
a
> problem with an IIF inside a SUM.

> The problem is that the sum needs to be positive if another field contains
a
> 0 and negative if that field contains a 1.  The original Sum command from
> the Access query went along the lines of:

> Select

Sum(iif(MyTable.FieldName=1,(-1*MyTable.AnotherField),MyTable.AnotherField))

- Show quoted text -

Quote:> AS SumFieldName FROM etc ...

> The IIF inside the sum would sum in  (-1 * AnotherField) if FieldName is
1,
> or would just sum AnotherField if FieldName was 0.

> I have tried replacing the IIF with an IF statement and I keep getting
> syntax errors.  Does anyone know how to achieve the same thing?

> Any help appreciated.

> --
> Siv
> Martley, Near Worcester, UK.

 
 
 

Help Finding SQL Server equivalent of IIF

Post by Siv » Tue, 13 May 2003 08:31:20


John,
Thanks a lot, your suggestion worked!!
Siv


Quote:> Hi Siv,

> Try something like this:

> SELECT SUM(CASE WHEN MyTable.FieldName = 1 THEN (-1 *

MyTable.AnotherField)
Quote:> ELSE MyTable.AnotherField END) AS SumFieldName FROM etc..

> John.

etc ...
 
 
 

1. IIF equivalent in SQL Server

The following SQL works in MS Access 97.
SELECT reject_category, reject_desc, Sum(IIF([PM] = "1",
[reject_tons],0)) AS PM1, Sum(IIF([PM]= "2",
[reject_tons],0)) AS PM2,
PM1 + PM2  as TotalTons
From waste_data_detail
where ((prod_date = #3/24/2002#))
Group By reject_category, reject_desc
order by reject_category

However, when trying to replace the IIF with Case
Structures in SQL Server, I get an error requiring that I
include PM in my grouping. Is there a way to get around
this problem?

Mike

2. How do I get in a Secured Access DB with VB4?

3. SQL Server IIf Equivalent?

4. Urgend! - Please help!!! - Oracle 7 Client over NET80

5. The equivalent of IIF in SQL Server

6. Sr. Oracle DBA (2 positions)

7. SQL Server equivalent of IIF?

8. empty date = 01/01/1900?

9. Sequel server equivalent of Access iif function

10. Sequel server 6.5 equivalent of iif function

11. Equivalent of IIF available in SQL ??

12. IIf equivalent in T-SQL ?!?

13. Iif() Equivalent