Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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
0
AlexPonnath
Asked:
AlexPonnath
  • 3
  • 3
  • 3
  • +1
2 Solutions
 
PortletPaulCommented:
try this please
SELECT
        iif( (sum(case when type = 'DS1' then 1 end) >0) , 'Y', 'N' )  as DS1
      , iif( (sum(case when type = 'DS3' then 1 end) >0) , 'Y', 'N' )  as DS3
FROM dbo.tbl_NoneImpairedLoop
WHERE wirecenter = 'LSANCA03'
GROUP BY
      wirecenter

Open in new window

0
 
AlexPonnathAuthor Commented:
code looks good but still produces the following error

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '>'.
0
 
Vikas GargBusiness Intelligence DeveloperCommented:
Hi,

Can be done by just using CASE

SELECT
CASE WHEN sum(case when type = 'DS1' then 1 else 0 end) > 0 THEN 'Y' ELSE 'N' END  as DS1
,CASE WHEN sum(case when type = 'DS3' then 1 else 0 end) > 0 THEN 'Y' ELSE 'N' END  as DS3
FROM dbo.Testing
WHERE wirecenter = 'LSANCA03'
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
PortletPaulCommented:
:) yes, better. what was I thinking
(and I prefer to avoid IIF() also)
0
 
Harish VargheseProject LeaderCommented:
IIF is available only from SQL Server 2012. Are you using lower version of SQL Server?
0
 
AlexPonnathAuthor Commented:
It's ms SQL 2008
0
 
Harish VargheseProject LeaderCommented:
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.
0
 
AlexPonnathAuthor Commented:
Is there a performance advantage one over the other if so I could try to upgrade SQL server to 2012 if it makes sense
0
 
PortletPaulCommented:
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
0
 
Harish VargheseProject LeaderCommented:
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.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now