Extracting process owners from a set using a Query

[hsoegaard]

Hi, I'm trying to extract a list of process owners (using the Business Person object) from a set of Organizational Processes. But I'm also getting Business Person that are assigned as Process Owners in processes that are not part of the set.

The query I'm using:

Select [Business Person] Into @orgpowner Where [Organizational Process].[Process Owner]="Yes" And [Organizational Process] in @app_process

Ideally I would have used the follwoing Query, but that is not valid query:

Select [Business Person] Into @orgpowner Where [Organizational Process].([Process Owner]="Yes" And [Name] in @app_process)

Is there a way to do this correctly in a Query?

[lionel]

And of course, you can also use a macro to implement your query 🙂

[hsoegaard]

Good idea .. I will try it out

[lionel · Accepted solution]

Well, your other problem in that case comes from the "deeply".

If you accept to get rid of it (you will then need to determine what is the max number of levels you can have) you can write your select this way (I've done it for 3 levels of "Owner Organizational Process"... add as many as you want...)

I'm sorry, I have no better way...

Select [Library] Into @publib1 Where [Parent Library].[Absolute Identifier] Deeply ="ZWicxhvkEzBP" Or [Absolute Identifier]="ZWicxhvkEzBP"

Select [Library] Into @publib2 Where [Parent Library].[Absolute Identifier] Deeply ="v9VHwPa)G9Y4" Or [Absolute Identifier]="v9VHwPa)G9Y4"

Select [Library] Into @publib3 Where [Parent Library].[Absolute Identifier] Deeply ="rAVHSQa)GnZ4" Or [Absolute Identifier]="rAVHSQa)GnZ4"

Select [Business Person] Into @orgpowner Where [Organizational Process].(

[Process Owner]="Yes" And

(

([Owner Packager] in @publib1 Or @publib2 Or @publib3) Or

([Owner Organizational Process].[Owner Packager] in @publib1 Or @publib2 Or @publib3) Or

([Owner Organizational Process].[Owner Organizational Process].[Owner Packager] in @publib1 Or @publib2 Or @publib3) Or

([Owner Organizational Process].[Owner Organizational Process].[Owner Organizational Process].[Owner Packager] in @publib1 Or @publib2 Or @publib3)

)

)

[hsoegaard]

Hi,

Yes we are extracting a list of all "approved" processes, the ones we would to publish and then keep them as a set, so all subsequent queries can use that set. This is done automatically when we publish. I dont think it would help copying that query into the above, since I would still end up with a set.

Set query below is building the set for the "approved" Org. processes:

Select [Library] Into @publib1 Where [Parent Library].[Absolute Identifier] Deeply ="ZWicxhvkEzBP" Or [Absolute Identifier]="ZWicxhvkEzBP"

Select [Library] Into @publib2 Where [Parent Library].[Absolute Identifier] Deeply ="v9VHwPa)G9Y4" Or [Absolute Identifier]="v9VHwPa)G9Y4"

Select [Library] Into @publib3 Where [Parent Library].[Absolute Identifier] Deeply ="rAVHSQa)GnZ4" Or [Absolute Identifier]="rAVHSQa)GnZ4"

Select [Organizational Process] Into @topbusp Where [Owner Packager] in @publib1 Or @publib2 Or @publib3

Select [Organizational Process] Into @aggbusp Where [Owner Organizational Process] Deeply in @topbusp

Select [Organizational Process] From @topbusp Or @aggbusp Into @app_process Keep @app_process

[lionel]

Hello Henrik,

I would love this syntax to work

Select [Business Person] Into @orgpowner Where [Organizational Process].([Process Owner]="Yes" And [Name] in @app_process)

it would have helped me many times !

Unfortunately, it does not 😞

How did you create your @app_process set ?

Is it to complicated to insert it directly ?

For example (a very simple example, I know yours must be quite more complicated...):

Select [Business Process] Into @app_process Where [Creation Date] > "01/01/2015"

Select [Business Person] Into @orgpowner Where [Organizational Process].([Process Owner]="Yes" And [Name] in @app_process)

Could be replaced by

Select [Business Person] Into @orgpowner Where [Organizational Process].([Process Owner]="Yes" And [Creation Date] > "01/01/2015")

Lionel