[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

Case When statement sql

I have a query like this


select
s.Description
,Status
,Received
,dateordered
 from employeesoftware es
Join Software s on es.AID =s.iid
 where employeeid=1

I would like to add  a role column.  the following returns all the roles for an employee in a single column. Can I add this to a case when? I tried but it gives me an error on the first join line

Declare @out varchar(100)
SELECT @out = COALESCE(@out +',' , '')+  rtrim(ar.Description) from employeeroles er
join Software s on er.applicationid =s.iid
Join [Application Roles] ar on  ar.iid=er.RoleID
where employeeid=786 and er.applicationID =23
select @out
0
JonMny
Asked:
JonMny
  • 2
  • 2
1 Solution
 
SQL_SERVER_DBACommented:
Declare @out varchar(100)

SELECT @out = COALESCE(@out +',' , '')+  rtrim(ar.Description) from employeeroles er
inner join Software s on er.applicationid =s.iid
inner Join [Application Roles] ar on  ar.iid=er.RoleID
where employeeid=786 and er.applicationID =23

select @out
0
 
UnifiedISCommented:
It's safe to say probably but please provide your conditions for the cases
0
 
JonMnyAuthor Commented:
What I am wondering is can I do somthing like this? or is it better to move the case when to a UDF

Declare @out varchar(100)

select
s.Description
,Role =
case when
(SELECT @out = COALESCE(@out +',' , '')+  rtrim(ar.Description) from employeeroles er
join Software s on er.applicationid =s.iid
Join [Application Roles] ar on  ar.iid=er.RoleID
where employeeid=786 and er.applicationID =23) is not null then ...
,Status
,Received
,dateordered
 from employeesoftware es
Join Software s on es.AID =s.iid
 where employeeid=1


0
 
SQL_SERVER_DBACommented:
you a sub qry and then apply your case when on the value pulled from the subqry
0
 
UnifiedISCommented:
I would continue the way you are heading.
I think you need to removed the @out =

case when
(SELECT  COALESCE(@out +',' , '')+  rtrim(ar.Description) from employeeroles er
join Software s on er.applicationid =s.iid
Join [Application Roles] ar on  ar.iid=er.RoleID
where employeeid=786 and er.applicationID =23) is not null then ...
END
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now