Highlighted
Absent Member.. Absent Member..
Absent Member..
964 views

Inbox view complex query

I have a need to show (inbox.view) an RFC window based on what time of day it is and to show a block of RFC for that window (may span days in the future or past). Don't ask about rules, it's many if/else rules about day/dates.

So I wrote a scriptLib to work out the logic and it returns a valid query expression. I call that function from the query field of my new view.

val(jscall("CIB_LIB.buildCIB"),11) This doesn't work. val (..., 2) also fails. Without val(I get ORA-0920 invalid ... so long as I return an exprestion it fails to evaluate it. I've excaped the " with \" it just doesn't eval correctly.

from testing the best I could figure out is Val() likes to compair so I change the function to return the one date I need and hardcoded the rest and it works.

header,category isin {"Normal Change", "Emergency Change"} and header,planned.start>=val(jscall("CIB_LIB.buildCIB","S"),3) and header,planned.start<=val(jscall("CIB_LIB.buildCIB","E"),3)

As long a val() is one side of an expression it works. If val() is the expression it becomes an equality and fails to select any records. Without val() it's literal and RAD fails somehow and passes Oracle invalid SQL, aka ORA-0920

If anyone thinks they know how to simplify this I'd like to know otherwise it's just a giveback if anyone has the same issue.

0 Likes
8 Replies
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Inbox view complex query

If you call your script from RAD Debugger, what returns from this expression:

x $L.test = jscall("CIB_LIB.buildCIB", "S")

d $L.test

d type($L.test)

 

Additionally, another way to resolve this is change your script from setting the date values used in the View definition, and instead return the change numbers... perform your query inside your javascript and return an array of CM numbers.  Then your View defintion could be like:
header,number isin jscall("CIB_LIB.getChangeIDs")

 

 

 

0 Likes
Highlighted
Acclaimed Contributor.
Acclaimed Contributor.

Re: Inbox view complex query

This is a use cases for the out of the box Change Calendar functionality.

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Inbox view complex query

>x $L.test = jscall("SS_CIB_VIEW.testCIB")
>d $L.test
header,category isin {"Normal Change", "Emergency Change"} and header,planned.start>='11/29/16 22:00:00' and header,planned.start<='11/30/16 05:00:00'
>d type($L.test)
2

names are different but the results are the same. this is the code that didn't work. TestCIB returns a string (2 char) that works in Expert Search for change and if paste into the query field also works but fails in Query field of the view when it's passed in.  testing (d $L.query shows it) I think it's reading it as an equality not an expression. Think of it as the == vs = trap when the system implies equality.

>x $num = 1
1 (this should have said false)
>d $num = 1
true (OR this should have said 1 but it implied equaily)
{i understand x and d but you see the problem if Query field used d when we expect x}
>x $num = 1 = 1
true


The idea to return the records as is pretty simple and I considered it (saw a post). I didn't  do that because I would have to search the table and return the string and View would then have to search the table again to get the records I just had. 

 

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Inbox view complex query

ok, so if your script returns the entirety of the query, what's the value of the 'Query' in the Query Definition tab of your View Definition record? And would it be possible to attach your script?  What you're trying to do does work - using Javascript to generate a query that is called from Expert Search, Inbox, Link Expressions, etc - so since it's NOT working in your system, there's something simple that we're missing.

And to clarify:

x $num=1

SHOULD return 1... you're setting the value of the variable ($num) to 1, and the system displays the value of that variable (1).  That 'x' in RAD debugger isn't about == vs =... it's setting the value explicitly.  If you used RAD Debugger and did:
x $num="pete"
the system would respond with pete, since that is the value of that variable you just set.

HPSM style code doesn't use the concept of ==; that's javascript only in HPSM, not regular code.

 

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Inbox view complex query

I'd rather not post the script but I agree with you, it should work. I also agree and undersand RAD debugger.

"value of the 'Query' in the Query Definition tab" I tried many:
jscall("CIB_VIEW.buildCIB")
val(jscall("CIB_VIEW.buildCIB"),2)
val(jscall("CIB_VIEW.buildCIB"),11) {11 is Expression and docs say don't use it but I tried it}
$L.a = val(jscall("CIB_VIEW.buildCIB"),2)
$L.a = jscall("CIB_VIEW.buildCIB")

they all fail for one reason or another. ORA-0920 invalid expressing is still a lead I should investigate. I didn't sqldebug but I probably will just to get a root cause. Interestingly, if I Val() ether side of an expression it works but the entire string doesn't. Thats why I posted the implided RAD rules around =. Not that RAD isn't doing it correct, just that it's making decitions.

0 Likes
Highlighted
Acclaimed Contributor.. Acclaimed Contributor..
Acclaimed Contributor..

Re: Inbox view complex query

I've seen some RAD code that is explicitly searching for a $ within a field and then evaluating the expression if there's one present.  Like, there'll be an expression within the rad like:
if (index("$", query in $L.view)>0) then ($L.query = evaluate(query in $L.view, 11))

 So, what if you tried setting your Query value to:

jscall("CIB_VIEW.buildCIB", $L.void)

Even if your script doesn't require the parameter to be passed, the existence of the $ variable might be the kick the tool needs to evaluate it, rather than try to run it as is.

Alternatively, what if you modified your script to return just the date portions of your query?  

In our environment, I've got javascript code that takes a few input parameters and then searches through CIs for related relationships and related audit records, and returns an array of CI Names.  

If I call my script from RAD Debugger, I get an output like:

x $L.void=jscall("cgAuditHistory.getCINamesByAssignment", "CI Owner Verification", "application", "Business App", $lo.pm.assignments, 90)

{"Change Management System", "EOSS Knowledge Center", "HP Service Manager", "HP UCMDB"}

So then in my View Definition query, I have:

logical.name isin jscall("cgAuditHistory.getCINamesByAssignment", "CI Owner Verification", "application", "Business App", $lo.pm.assignments, 90) and istatus="In use"

Using the Javascript to generate part of the query, and the query field itself handling the rest.

 

 

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Inbox view complex query

I had working code in the first post with just the dates returned as the right side of the expressions. I posted to help others see what issue I had and how it had to solve it, although it's not ideal.

"if (index("$", query in $L.view)>0) then ($L.query = evaluate(query in $L.view, 11))"

That's very cool! I tried a few hacks like jscall("SS_CIB_VIEW.testCIB","$") some with Eval ... no joy, still trying so far but I think that's why my val() works as part of the expression. It's can't eval the entire string because {insert confused look}

I have the Ora error root cause. sqldebug shows that the SQL is a raw expression and that BindVars are way wrong:

4719( 29969) 11/30/2016 15:10:38 RTE D _ociBindAllByPos : N/A #1: SQLT_AFC : 0x0A39DFB8-150 data = header,category isin {"Normal Change", "Emergency Change"} and header,planned.start>='11/30/16 22:00:00' and header,planned.start<='12/01/16 05:00:00'

this should be normalized bind vars [aka,(Y:)}  that Oracle can understand so that's the root cause of that.

4719( 29969) 11/30/2016 14:58:22 RTE D _ociBindAllByPos : N/A #1: SQLT_AFC : 0x0A39DFB8-13 data = Normal Change
4719( 29969) 11/30/2016 14:58:22 RTE D _ociBindAllByPos : N/A #2: SQLT_AFC : 0x0A39DFC8-16 data = Emergency Change
4719( 29969) 11/30/2016 14:58:22 RTE D _ociBindAllByPos : N/A #3: SQLT_AFC : 0x0A39DFE0-20 data = 2016-12-01 03:00:00
4719( 29969) 11/30/2016 14:58:22 RTE D _ociBindAllByPos : N/A #4: SQLT_AFC : 0x0A39DFF8-20 data = 2016-12-01 10:00:00

So, that means any attempt to evaluate the entire string will fail to parse out the parms from the sql and pass in bind vars because it's already past (or skips over) the part of RAD that parses $L.Query. That's why _ociBindAllByPos is the expression. If it was before, we'd see parts of the expression where parse failed. 

That was very helpful

 

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

Re: Inbox view complex query

val(jscall("SS_CIB_VIEW.testCIB"),11)

this runs without the ORA error with the entire expression as the return but RAD evals it to TRUE and you get every record. sqldebug bind var is 't' or what we know as open=true.

I think the only way to do it in a view is to use val(jscall(...)) on the right side of the equality or my system (9.31) is broken (a possibility)

an example
header,planned.start>=val(jscall("scriptlib.function"),3) 

3 is a date/time. 2 is char .... read the doc for types and use what you need.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.