PUG

  • PUG News
  • PUG Forum
  • Shared documents
  • PUG Events
    • Scandinavian Conferences
    • Scandinavian Theme Days
    • D-A-CH Events
    • North American Events
  • PUG Info
    • PUG Board Info (Scandinavia)
    • PUG Rules
    • PUG Member Lists and Board Members
  • Contact Us

Reply To: POB soft lock column

  • Follow us on Twitter
  • Join our Facebook Group
  • RSS

Welcome to the POB User Group Online Community! › Forums › PUG International › POB soft lock column › Reply To: POB soft lock column

03/09/2019 at 08:44 #4680
Stefan Reichelt
Participant

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

Login

Register | Lost your password?

Menu

  • PUG News
  • PUG Forum
  • Shared documents
  • PUG Events
    • Scandinavian Conferences
    • Scandinavian Theme Days
    • D-A-CH Events
    • North American Events
  • PUG Info
    • PUG Board Info (Scandinavia)
    • PUG Rules
    • PUG Member Lists and Board Members
  • Contact Us

Pages

  • Anmeldung zur PUG Konferenz 2018
  • Contact us
  • Forgot Password
  • Login
  • Lost password
  • POB Tip – Filtering lists to show only limited data
  • Profile
  • Registration
  • Registration for PUG 2019 in Scandinavia
  • Registration for PUG 2020 in Scandinavia
  • Skandinavisk PUG konference 2014 (Hurdal, Norge)
  • Skandinavisk PUG konference 2017 (Sundvolden, Norge)
  • Tilmelding til / Anmälan till PUG 2015 i Sollentuna
  • Tilmelding til erfamøde hos Frederiksberg Kommune
  • Tilmelding til PUG 2016 / Anmälan till PUG 2016
  • Tilmelding til PUG 2018 / Anmälan till PUG 2018
  • Udfyld nedenstående felter hvis du ønsker at melde jer ind i PUG
  • Welcome to the POB User Group Online Community!
  • Wendia North America 2018 Customer-Partner Conference Registration Form
  • Wendia North America 2019 Customer-Partner Conference Registration Form
  • You are not a member?
  • Webinar on the New Mobile Web Apps in POB G6 2.006/01/2021 - 13:41

    The POB G6 2.0 mobile interfaces for analysts and customers provide great new capabilities and features. The mobile interfaces have a new look and feel and they allow utilization of most areas of POB from a mobile platform. On January 19th, Kevin Goertzen will show and demonstrate the new mobile interfaces in POB G6 2.0 […]

  • December Webinar on Triggers in POB27/11/2020 - 12:10
  • PUG 2021 – Redesigned for a Virtual Setting (Scandinavia)13/11/2020 - 11:29

    The PUG board has decided to have next year’s Scandinavian conference in a virtual setting. So, mark your calendar for April 20th, 2021, where PUG will host a one-day virtual conference. The decision was made based on the feedback the PUG board got when they asked how participants would feel about attending a physical conference, […]

© Copyright - PUG - All rights reserved by PUG © 2012
  • Send us Mail
  • Follow us on Twitter
  • Join our Facebook Group
  • Subscribe to our RSS Feed