I'm not sure - it worked in the previous version of ARS (8.1) but not in 9.
The actual workflow qualification was this, and $z1d Action02$ was null when
this ran:
('Decision Tree ID' = $Decision Tree ID$) AND ('Main Branch' = "Yes") AND ( NOT
($z1D Action02$ LIKE (("%" + 'Branch ID') + "%"))) AND ('Status' = "Enabled")
Decision trees are kind of unique in the app - most of the time dialog windows
open up for people to do things like associate a People record to a group or
stuff like that. In decision trees the window is re-used and refreshed several
times. $z1D Action02$ is a display only hidden field that ends up holding the
Branch ID of the current branch - but the first time time through it is null
and fails and the window closes.
William Rentfrow
wrentf...@stratacominc.com
Office: 715-204-3061 or 701-232-5697x25
Cell: 715-498-5056
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of Grooms, Frederick W
Sent: Monday, November 02, 2015 8:51 AM
To: arslist@ARSLIST.ORG
Subject: Re: ITSM 9.0 question - can you test this? - RESOLVED
**
I thought the NOT LIKE didn't work to begin with
I have always coded it as
'Status'=1 and (NOT ('AppleSauce' LIKE "%"+$STUFF$+"%"))
Fred
From: Action Request System discussion list(ARSList)
[mailto:arslist@ARSLIST.ORG] On Behalf Of William Rentfrow
Sent: Monday, November 02, 2015 8:42 AM
To: arslist@ARSLIST.ORG<mailto:arslist@ARSLIST.ORG>
Subject: Re: ITSM 9.0 question - can you test this? - RESOLVED
**
Guess what? I found my first ARS 9.0 base product bug.
This only applies to Oracle - I had two different people try it on SQL server
and BMC also tried it on both SQL and Oracle - SQL Server is fine, Oracle
doesn't work. The AR Server constructs bad SQL.
If you have any work flow that does something like a NOT LIKE and a field and
the field is $NULL$ the query is going to break. So this:
'Status'=1 and 'AppleSauce' NOT LIKE "%"+$STUFF$+"%"
(where $STUFF$ is null)
.....will cause it to fail when querying in Oracle.
BMC registered a defect for it. Fortunately this kind of query is pretty rare.
The only time I've seen it is in the base product Decision Trees where the
screen pops up on the first branch. I devised a code workaround for it and
everything worked.
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 10:10 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.
_ARSlist: "Where the Answers Are" and have been for 20 years_
_______________________________________________________________________________
UNSUBSCRIBE or access ARSlist Archives at www.arslist.org
"Where the Answers Are, and have been for 20 years"
