Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


IIF-function gives error


IIF-function gives error

Author
Message
r_slot
r_slot
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 176
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
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
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
When a question, really isn't a question - Jeff Smith
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


r_slot
r_slot
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 176
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...
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
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
When a question, really isn't a question - Jeff Smith
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


r_slot
r_slot
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 176
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
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078
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
When a question, really isn't a question - Jeff Smith
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


Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
r_slot
r_slot
SSC Journeyman
SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)SSC Journeyman (79 reputation)

Group: General Forum Members
Points: 79 Visits: 176
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
anthony.green
anthony.green
SSCertifiable
SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)SSCertifiable (6.1K reputation)

Group: General Forum Members
Points: 6100 Visits: 6078

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
When a question, really isn't a question - Jeff Smith
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


Adi Cohn-120898
Adi Cohn-120898
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2224 Visits: 6079
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/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search