Error with IIF - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Error with IIF

Author

Topic

djavet
Starting Member

36 Posts

Posted - 2005-06-28 : 02:29:44

Hello I've a simple query wich use the IIF function in MS SQL and I receive a error:

SELECT 
  heures,
  IIF(1 = 0, 2, 3) AS Test
FROM
  heures_sap

Error:
Line 3: Incorrect syntax near '='

This query is a extract from my real query ;o)

SELECT 
  succursales.succursale,
  Sum(heures_sap.heures) AS sub_total_heures,
  Sum(IIf(ordre_rec = '24001000' Or ordre_rec = '24001001' Or ordre_rec = '24001020', heures, 0)) AS administration,
  Sum(IIf(typact = 'ARMEE' Or typact = 'FERIEN' Or typact = 'KRANK', heures, 0)) AS ctre_cout,
  Sum(IIf(ordre_rec = '24001000' Or ordre_rec = '24001001' Or ordre_rec = '24001020' Or typact = 'ARMEE' Or typact = 'FERIEN' Or typact = 'KRANK', 0, heures)) AS productif,
  (Select Sum(heures) FROM heures_sap where heures_sap.heures > 0) AS grand_total_heures
FROM
  heures_sap
  LEFT OUTER JOIN succursales ON (heures_sap.ctre_emet = succursales.ctre_emet)
WHERE
  (heures_sap.heures) AND 
  (0)
GROUP BY
  succursales.succursale

The result must be:
succursale sub_total_heures administration ctre_cout productif grand_total_heures
CNP 964.78 148.6 204 612.18 10150.79
CSC 2246.96 492.3 321.5 1433.16 10150.79
FM Fribourg 1439.27 69.15 172 1198.12 10150.79
FM Genève 1397.77 118.8 172 1106.97 10150.79
FM Tessin 1572.85 207.5 304 1061.35 10150.79
FM Valais 682.59 86.45 80 516.14 10150.79
FM Vaud 1360.32 241.3 241.69 877.33 10150.79
Leitung, RC, SA 486.25 27.5 113.25 345.5 10150.79

What I am doing wrong?
Thx for your help.

Regards, Dominique

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-06-28 : 02:44:23

IIF is for analysis services only, it is not used in regular transact sql.

Look into using the CASE statement instead.

-ec

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-28 : 02:45:10

Did your real query work without any error?
Instead of IIF, try to use Case


SELECT 
  heures,
  (case when col='value' then 2 else 3 end ) AS Test
FROM
  heures_sap

Madhivanan

Failing to plan is Planning to fail

djavet
Starting Member

36 Posts

Posted - 2005-06-28 : 04:28:39

quote:
Originally posted by madhivanan

Did your real query work without any error?

Nope, I've the same error...

Regards, Dom

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-28 : 04:38:32

IIF isnt a SQL Server function
Is this query being used in Access against SQL server or are you trying to get this to work in QA/SQL server

If its the latter then you need to take eyecharts & madhivanans and look up CASE instead

Andy

Beauty is in the eyes of the beerholder

djavet
Starting Member

36 Posts

Posted - 2005-06-28 : 04:40:40

Yes, it's from MS Access to MS SQL.
So I will try to work this out with CASE.
I'm a newbie and must learn in pain

Regards, Dom

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-06-28 : 05:00:26

Here is something which will help you get started


SUM(CASE WHEN ordre_rec IN ('24001000','24001001','24001020') THEN heures ELSE 0 END) AS administration

Andy

Beauty is in the eyes of the beerholder

djavet
Starting Member

36 Posts

Posted - 2005-06-28 : 08:14:19

Thx a lot!
I work it out

quote:
MS ACCESS

SELECT succursales.succursale, Sum(heures_sap.heures) AS sub_total_heures, Sum(IIf([ordre_rec]='24001000' Or [ordre_rec]='24001001' Or [ordre_rec]='24001002' Or [ordre_rec]='24001003' Or [ordre_rec]='24001005' Or [ordre_rec]='24001020',[heures],0)) AS administration, Sum(IIf([typact]='ARMEE' Or [typact]='FERIEN' Or [typact]='DIVERS' Or [typact]='BILDUN' Or [typact]='KRANK',[heures],0)) AS ctre_cout, Sum(IIf([ordre_rec]='24001000' Or [ordre_rec]='24001001' Or [ordre_rec]='24001002' Or [ordre_rec]='24001003' Or [ordre_rec]='24001005' Or [ordre_rec]='24001020' Or [typact]='ARMEE' Or [typact]='FERIEN' Or [typact]='DIVERS' Or [typact]='BILDUN' Or [typact]='KRANK',0,[heures])) AS productif, (Select Sum(heures) FROM heures_sap where heures_sap.heures > 0) AS grand_total_heures
FROM heures_sap LEFT JOIN succursales ON heures_sap.ctre_emet = succursales.ctre_emet
WHERE (((heures_sap.heures)<>0))
GROUP BY succursales.succursale

succursale sub_total_heures administration ctre_cout productif grand_total_heures
CNP 964.78 178.05 204 582.73 10150.79
CSC 2246.96 535.3 322.5 1389.16 10150.79
FM Fribourg 1439.27 99.97 180 1159.3 10150.79
FM Genève 1397.77 132.3 172 1093.47 10150.79
FM Tessin 1572.85 209 320 1043.85 10150.79
FM Valais 682.59 102.7 88.5 491.39 10150.79
FM Vaud 1360.32 251.8 273.69 834.83 10150.79
Leitung, RC, SA 486.25 120.5 113.25 252.5 10150.79

And the result for SQL:

quote:
MS SQL

SELECT
succursale,
SUM(heures) AS sub_heures,
SUM(CASE WHEN ordre_rec IN('24001000', '24001001', '24001002', '24001003', '24001005', '24001020') THEN heures ELSE 0 END) AS administration,
SUM(CASE WHEN typact IN('ARMEE', 'FERIEN', 'KRANK', 'DIVERS', 'BILDUN') THEN heures ELSE 0 END) AS ctre_cout,
SUM(heures) - SUM(CASE WHEN ordre_rec IN('24001000', '24001001', '24001002', '24001003', '24001005', '24001020') OR typact IN('ARMEE', 'FERIEN', 'KRANK', 'DIVERS', 'BILDUN') THEN heures ELSE 0 END) AS productif,
(SELECT SUM(heures) FROM heures_sap WHERE heures_sap.heures > 0) AS grand_total_heures
FROM
heures_sap
LEFT OUTER JOIN succursales ON (heures_sap.ctre_emet = succursales.ctre_emet)
WHERE
(heures_sap.heures > 0)
GROUP BY succursale
ORDER BY succursale

succursale sub_heures administration ctre_cout productif grand_total_heures
CNP 964.78 178.05 204 582.73 10150.79
CSC 2246.96 535.3 322.5 1389.16 10150.79
FM Fribourg 1439.27 99.97 180 1159.3 10150.79
FM Genève 1397.77 132.3 172 1093.47 10150.79
FM Tessin 1572.85 209 320 1043.85 10150.79
FM Valais 682.59 102.7 88.5 491.39 10150.79
FM Vaud 1360.32 251.8 273.69 834.83 10150.79
Leitung, RC, SA 486.25 120.5 113.25 252.5 10150.79

A lot of thx for your help and time.
Another day, another knowledge.

Sub Question:
Is this normal that the SQL query with CASE doesn't work in MS Access?
Access said: Syntax error (Missing operator)

Regards, Dominique

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-28 : 08:57:52

you have to use IIF in MS Access

Madhivanan

Failing to plan is Planning to fail

Crito
Starting Member

40 Posts

Posted - 2005-06-28 : 16:43:20

IIF is a VB/VBScript function. Access just lets you use VBScript functions in SQL queries, same way Sybase lets you use Java functions in SQL queries (they even call it SQLJ) and SQL Server 2005 lets you use C# functions in SQL queries.

To an SQL purist like myself they're all abominations. :o

----------------------------------
Gun for hire, have horse, will travel.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources