Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

IIF-function gives error Expand / Collapse
Author
Message
Posted Tuesday, January 29, 2013 4:37 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
Folks,

I am using SQL Server 2012 Express Edition and want to make use of the new IIF-function. I am using the following code:

IIF(Rank=1,'Gold',IIF(Rank =2,'Silver', 'Bronze'))


Rank is a databasefield.
My problem is that (when working in the criteria pane of Studio) I am getting the following error:
An expression of non-boolean type specified in a context where a condition is expected, near '('
It looks like IIF is not recognized??

Working in de sql pane of Studio the error is something like:
Error in list of function arguments: '=' not recognized.
Unable to parse query text.
.

I have tried almost everything to get the problem solved, but nothing seems to work.

Can anyone get me out of this mess?

Grz,

Robert
Post #1412908
Posted Tuesday, January 29, 2013 4:40 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
You need to qualify what Rank is, if its a field in a Data Set then you need to do something like the below.

=IIF(Fields!Rank.Value =1,'Gold', IIF(Fields!Rank.Value =2, 'Silver', 'Bronze'))




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412912
Posted Tuesday, January 29, 2013 5:04 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
Rank is just a databasefield and I am referring to it as usual. It must be possible (as with other functions) to use this function in the criteria pane referring to the tables involved. When I write dbo.Sometable.Rank the same error comes forward, so the problem must be somewhere else I think...
Post #1412934
Posted Tuesday, January 29, 2013 5:07 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
In an expression you need to refer to the column as a Field using Fields! then you give it the field name so Fields!Rank then you get the property, in this case the value so Fields!Rank.value.

Your not writing SQL code in expressions, your writing .Net code.

=IIF(Fields!Rank.Value = 1, 'Gold') Which means that is the field called rank has a value of 1, colour it gold.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412938
Posted Tuesday, January 29, 2013 5:45 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
Anthony,

Thanks for your answers. I tried your solution but that doesnot make any difference. The problem are the panes in Studio: when I use the criteria pane the IIF-function gives an error.
When I am building a view in scripting mode everything works fine, when I am only using the sql-pane I get an error ('=' nog recognized) but the query runs.
So I used the following code and in scripting mode it works OK:

IIF(Rank=1,'Gold',IIF(Rank=2,'Silver','Bronze'))

The advantage over CASE is that the IIF uses only one column.
Thanks for your advise.

Grz,

Robert
Post #1412964
Posted Tuesday, January 29, 2013 5:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
Then I would ask that you post the table definition, some sample data and what the expected outcome should look like on the report.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412967
Posted Tuesday, January 29, 2013 5:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 2,112, Visits: 5,477
Since you posted this question also in SQL Server newbies under SQL Server 2008, I have a feeling that your question has nothing to do with Reporting Services. Can you write if the question is about the syntax in Reporting Services or is it about the way that the function IIF should be used in SQL Server 2012? Also it would be helpful if you'll post the questions in the correct forums.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1412969
Posted Tuesday, January 29, 2013 5:54 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Monday, March 3, 2014 12:25 AM
Points: 73, Visits: 158
I would like to but it is not necessary, because we have some misunderstanding. I placed the post in the wrong forum (being reporting services) with the effect of replies like yours. The fact is that I am not building a report so the code I use in queries is a little bit different then the code in reports. My post belongs to the sql server newbies section and I asked the moderator to move it to that forum.
Sorry for the inconvenience.

Robert
Post #1412970
Posted Tuesday, January 29, 2013 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:23 AM
Points: 5,216, Visits: 5,107
CASE Rank WHEN 1 THEN 'Gold' WHEN 2 THEN 'Silver' ELSE 'Bronze' END AS RankColour

So your after something like the above?

IIF wont work in SQL as its not a SQL construct, its a coding language construct.

If the above is not what you want please provide the tables, sample data and the expected outcome and I am sure we can get a resolution. You can take a look in the second link in my signature on the way to post the information so that we can make a quick testing environment based on what you provide.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1412973
Posted Tuesday, January 29, 2013 6:06 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:19 AM
Points: 2,112, Visits: 5,477
IIF works on SQL Server 2012. I've written you an example in the other thread that you've opened (http://www.sqlservercentral.com/Forums/Topic1412915-1292-1.aspx?Update=1) because it has nothing to do with Reporting Services.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1412978
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse