Link to home
Start Free TrialLog in
Avatar of AlexPonnath
AlexPonnathFlag for United States of America

asked on

iif / Count Query

I am having some issues with syntax for the following MS SQL Query

SELECT iif((count(type) > 0)'Y','N')
FROM dbo.tbl_NoneImpairedLoop where wirecenter = 'LSANCA03'
group by wirecenter

I basically want to return a Y if there is more then 1 record and N if there is no record found.

Also in my world there can be either 0, 1 and 2 records. The difference of the records is in the field type
which can have wither a value of DS1 or DS3. Is there a single query which can return a Y or N for
a record based on value in type field

Ideally I would like to get basically something like

DS1 |  DS3
===========
Y      |  N

I know this might be to much to ask but hopefully someone has an idea..

Thanks
SOLUTION
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of AlexPonnath

ASKER

code looks good but still produces the following error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
:) yes, better. what was I thinking
(and I prefer to avoid IIF() also)
IIF is available only from SQL Server 2012. Are you using lower version of SQL Server?
It's ms SQL 2008
You cannot use IIF then. Vikas Garg's suggestion above is the best for your need, since you know that the values in your field can be either DS1 or DS3 only.
Is there a performance advantage one over the other if so I could try to upgrade SQL server to 2012 if it makes sense
do you mean performance of

case expression or IIF()

I would not expect any. Case expressions are SQL standard and well proven.
I think they are very likely to share code and be parsed/evaluated in the same way.

...but I've been wrong before now
I guess @PortletPaul is right here because I cannot see any difference in plan between these two, and you need not upgrade to 2012 just for this purpose. But there are so many new exciting features in SQL Server 2012 that will also improve performance. Here is a good compilation of the new features in SQL Server 2012.