Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #1 
SelectSql(new NBI(),
"SELECT c.cuser FROM ConflictsTeam as c
INNER JOIN CMS.CMS.dbo.PERSNL as p ON RTRIM(p.offc) = RTRIM(c.coffice)
INNER JOIN MBPM.dbo.eFolder AS f ON f.eOriginator = 'PLLLP\\' + RTRIM(p.login)
WHERE f.eFolderID = @FolderID", SQLArg("@FolderID", ProcessContext.FolderId)).List

It works in a calculated field, it works in sql, but when i try using it on a stage it causes the engine to lock up.  Any ideas?
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #2 
My first impression is that the 'where' clause may be somewhat incorrect. Although it may work, I would expect all conditions of the 'inner join' table to be on the join. This one is 'outside' and will be preformed after the join. It may be pedantic, but it can make a huge difference in large databases.

Generally, if there is a condition on the 'f' table (in this case), I would expect that table to be the primary table, and the other table to be joined to that. It gives the DBMA better options to optimise the query.

But, onto the real issue, I would try adding that role as an action, and see if that action appears, is hidden, or an error occurs. The only other possibility is that there is some conflict due to it being a query on an external database, but I am not aware of any restrictions there.

Having said that, it appears that there is a problem with case sensitivty, even in a case-insensitive database, but that would NOT cause an error, just a failure for the role to work.

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #3 
I've tried it with upper and lowercase.  i also re-wrote it as

SelectSql(new NBI(),
"SELECT c.cuser FROM ConflictsTeam as c where c.coffice = (SELECT offc FROM CMS.CMS.dbo.PERSNL as p
WHERE  "PLLLP\\" + RTRIM(p.login) = (SELECT f.eOriginator FROM MBPM.dbo.eFolder AS f
WHERE f.eFolderID = @FolderID))", SQLArg("@FolderID", ProcessContext.FolderId)).List

And it still doesn't work.  But if i put that in a calculated field it works fine.

Edit: Does it have anything to do with having a backslash in the usernames?

Edit 2: It does work if i apply it to an action, but not to a stage.
0
JoeOmerta

Veteran
Registered:
Posts: 210
Reply with quote  #4 
I'm thinking it has to do with the external database.  I set it up so it writes to a table the users office and efolder id, then the role polls that office based on folder id and references the conflictsteam table.  So it's working, it's not as dynamic as i'd like it to be as i can't just "throw in it production" without some prior form of work, but it's doable.  still waiting on a response from opentext.
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #5 
I think there is a conflict with the lookup on the efolder table when it is doing the role evaluation. Can you try a 'with (nolock)' perhaps?
__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!