January 29, 2013 at 4:37 am
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
January 29, 2013 at 4:40 am
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'))
January 29, 2013 at 5:04 am
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...
January 29, 2013 at 5:07 am
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.
January 29, 2013 at 5:45 am
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
January 29, 2013 at 5:47 am
Then I would ask that you post the table definition, some sample data and what the expected outcome should look like on the report.
January 29, 2013 at 5:49 am
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/
January 29, 2013 at 5:54 am
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
January 29, 2013 at 6:00 am
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.
January 29, 2013 at 6:06 am
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/
January 29, 2013 at 6:21 am
Anthony,
IIF is a new SQL-function in SQL Server 2012 and should work. Your suggestion though works like a charm in all panes so I think I'll use that one.
Thanks a lot. Afterall is makes not much difference in which forum the post is dropped, only the confusion factor gets maximized. Sorry for that.
Thanks again,
Robert
January 29, 2013 at 6:25 am
Appreciate that IIF is new in 2012, just with it being in a 2008 forum, I automatically assumed IIF = SSRS IIF not a SQL IIF.
Hence why it is always best to put topics in the right forum for the version of SQL your running as you say, saves on the confusion.
Also personally, I like CASE better than IIF, as I find nested IIF's can get a bit messy, where as CASE is easy to read.
January 29, 2013 at 6:30 am
Agree
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply