Reply To: POB soft lock column

[Stefan Reichelt]

Hi,

I’m not sure how deep you’re into SQL, but you’ve obviously already discovered the tricky part: If there is more than one result, your query will return more than one line – and that doesn’t fit into one single cell. However, SQL has some nice functions to merge such results. As a first step, you should transfer the logic into something like a Stored Procedure, Function, or View. I’ll show you the View option:

CREATE VIEW [dbo].[Custom_View_CaseSoftLocks]
AS
SELECT case_pk
	,users = STUFF((
			SELECT ', ' + sl.user_id
			FROM Pob_Soft_Lock sl with(nolock)
			WHERE sl.root_pk = cas.case_pk
			FOR XML PATH('')
			), 1, 1, '')
FROM Case_Table cas

Hint: Test the Select part with “where cas.id = ‘<any case ID>'” in your SQL Management Studio before you proceed.

And now you can query that in your column definition:
(select top 1 sl.users from dbo.Custom_View_CaseSoftLocks sl where sl.case_pk=case_pk)

The “top 1” is not necessary, but I usually take it as an additional security to make really sure to have only one result.

Br,

Stefan