I am pretty sure this is going to end up being DB-platform specific. We are on
Oracle 11g RAC.
I did this advanced search against CFG:Decision Tree Branch:
NOT ('Branch ID') LIKE $NULL$
It returns zero results. Apparently you can't do a NOT LIKE NULL on our DB
platform and have it work.
This doesn't work either, which is what is much closer to the SQL issued:
NOT ("%"+'Branch ID'+"%") LIKE $NULL$
This works just fine:
'Branch ID' != $NULL$
Obviously an overlay + fix on the Set Fields qual should fix this permanently.
William Rentfrow
wrentf...@stratacominc.com
Office: 715-204-3061 or 701-232-5697x25
Cell: 715-498-5056
From: William Rentfrow
Sent: Monday, October 26, 2015 10:11 PM
To: William Rentfrow; arslist@ARSLIST.ORG
Subject: RE: ITSM 9.0 question - can you test this?
Okay so I found the problem - kind of...
The workflow that generates that SQL is the first Set Fields of active link
CFG:PDB:RefreshBranchTable_100 - here is the set fields matching condition
doing a lookup against CFG:Decision Tree Branch:
('Decision Tree ID' = $Decision Tree ID$) AND ('Main Branch' = "Yes") AND ( NOT
($z1D Action02$ LIKE (("%" + 'Branch ID') + "%"))) AND ('Status' = "Enabled")
$z1D Action02$ is $NULL$ at that point in the workflow. It is set after this
to the Entry ID of the record in the 2nd Set Fields action of the same active
link (third overall action).
Here's what the SQL generated from that looks like:
SELECT * FROM (SELECT AR_SQL_Alias$1.*, rownum as AR_RowNumber_Alias$1 FROM
(SELECT T1031.C1000004130, T1031.C1, T1031.C1000004224, T1031.C1000004239,
T1031.C1000004241 FROM T1031 WHERE ((T1031.C1000004215 = N'PDT000000000002')
AND (T1031.C1000004224 = 0) AND (NOT (N'%'||T1031.C1||N'%' LIKE NULL)) AND
(T1031.C7 = 1)) ORDER BY T1031.C1000004215 ASC, T1031.C1000000244 ASC, T1031.C1
ASC) AR_SQL_Alias$1) WHERE ((AR_RowNumber_Alias$1 > 0) AND
(AR_RowNumber_Alias$1 < 3))
In our dev environment I created an overlay on that Active Link I changed the
"Set Fields" match condition to this:
('Decision Tree ID' = $Decision Tree ID$) AND ('Main Branch' = "Yes") AND
('Status' = "Enabled")
....and it works. So that whole C1 NOT LIKE NULL thing is what is causing
this. I am 100% sure that is the root cause.
I just have no idea what this is actually saying from the above SQL.
AND (NOT (N'%'||T1031.C1||N'%' LIKE NULL))
William Rentfrow
wrentf...@stratacominc.com<mailto:wrentf...@stratacominc.com>
Office: 715-204-3061 or 701-232-5697x25
Cell: 715-498-5056
From: William Rentfrow
Sent: Monday, October 26, 2015 12:48 PM
To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>
Subject: ITSM 9.0 question - can you test this?
Hello listers -
We've run into an interesting issue. In IM 9.0 we can select New Incident,
then fill out a caller's information.
The we select the decision tree link/button and it opens the trees.
Then when we select one it should open the branch window. It opens an empty
window.
In ITSM 8.1 it works - and it's all the same base product code. If you happen
to have ITSM 9 and a couple of seconds to spare can you try this?
Also for fun I compared the SQL and ARS 9 has a bunch of stuff I don't get -
like random "N"'s in the SQL
Older working server query:
SELECT * FROM ( SELECT
T1031.C1,C1000004224,C1000004241,C1000004130,C1000004239 FROM T1031 WHERE
((T1031.C1000004215 = 'PDT000000000002') AND (T1031.C1000004224 = 0) AND NOT
((' ' LIKE (('%' || T1031.C1) || '%'))) AND (T1031.C7 = 1)) ORDER BY
C1000004215 ASC,C1000000244 ASC, 1 ASC ) WHERE ROWNUM <= 2
New-fangled what is this stuff query:
SELECT * FROM (SELECT AR_SQL_Alias1 FROM (SELECT T1031.C1, T1031.C1000004239,
T1031.C1000004130, T1031.C1000004241, T1031.C1000004224 FROM T1031 WHERE
((T1031.C1000004215 = N'PDT000000000002') AND (T1031.C1000004224 = 0) AND (NOT
(N'%'||T1031.C1||N'%' LIKE NULL)) AND (T1031.C7 = 1)) ORDER BY
T1031.C1000004215 ASC, T1031.C1000000244 ASC, T1031.C1 ASC) AR_SQL_Alias1 > 0)
AND (AR_RowNumber_Alias$1 < 3))
William Rentfrow
wrentf...@stratacominc.com<mailto:wrentf...@stratacominc.com>
Office: 715-204-3061 or 701-232-5697x25
Cell: 715-498-5056
________________________________
No virus found in this message.
Checked by AVG - www.avg.com<http://www.avg.com>
Version: 2014.0.4830 / Virus Database: 4365/10845 - Release Date: 10/18/15
Internal Virus Database is out of date.
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
