That's possible, and there's no escaping it (pun intended). The first thing the code is going to do for IN(..) queries is split the input on ',' and then process each individual item. It doesn't check before or after for some escape sequence or decode operation to use on the data.
So you could handle it a few different ways.. here's one approach.
1. Add a second column in your NSQL query for resourcename, call it resnamenocomma
2. Use your DBMS vendor's replace() function to switch commas to some other value that you know could not ever be confused for a match.
3. In your client code that builds/submits the SOAP call, also replace any commas in the input values provided with this same substitute pattern.
4. Build your resnamenocomma_in value with the chosen entries, now possible to separate each one by a real comma.
5. Run it, you should now get the multiple results you sought.
This is kind of what I mean regarding the use of the replace functions on the individual inputs, just to demo the intent concretely:
select replace('hello, world!', ',',':COMMA:') from dual
hello:COMMA: world!Should be no problem then to build a comma-delimited list with the data. Filter on this column, return/show the other column with the original data.
Original Message:
Sent: 08-07-2019 02:19 PM
From: malay thaker
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
Hi Nick,
Thanks for the info! However, i did try that out already and the issue seems to the fact that the field is a combined string of lastname, first name. As a result, the value will contain a ',' in it.. Is there a way to make it search for the comma aswell as raw text?
Original Message:
Sent: 08-07-2019 01:57 PM
From: Nick Darlington
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
If you use a tool like Smartbear's Soap UI (not the Pro one but the community edition which is free) to examine the WSDL for each query, you'll see there are multiple filter parameter definitions for each 'column' of the NSQL, like this:
<!--Optional:-->
<quer:name>?</quer:name>
<!--Optional:-->
<quer:name_from>?</quer:name_from>
<!--Optional:-->
<quer:name_to>?</quer:name_to>
<!--Optional:-->
<quer:name_in>?</quer:name_in>
The question marks are just placeholders indicating something can go there. So instead of what you have, you could probably use the _in variant and supply both values there, like this:
<quer:resourcename_in>resource1,resource2</query:resourcename>
- Each entry is separated by a comma
- Do not use spaces after the commas as that will be included in the term and cause it not to match (unless the data really had a space there)
- Spaces can be used in the terms if they are suitable for the data, e.g:
<quer:name_in>comma,separated,list,of,values,no,spaces,after,comma,but can have spaces inside a single term</quer:name_in>
- If using the _in form to get all your values, then you can remove all the <quer:resourcename>…</quer:resourcename> entries, they won't be needed anymore.
- If using a tool like SoapUI to test with, strip out all the entries with ? that you aren't using or else it will actually push those into the request as if you wanted them intentionally, which will then probably error out.
Hope that helps.
Original Message:
Sent: 08-07-2019 12:04 PM
From: malay thaker
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
Hi Nick,
thanks for getting back to me!! I tried to look into this, but my nsql skills arent the best and im relatively new with clarity so learning the current back ed structure is a bit beyond me. however, my main goal of this question was to filter for multiple resource names. When im trying to make the call in soapui, it wont let me have multiple names or filter tags for the tag.
<quer:resourcename>resource1</quer:resourcename>
<quer:resourcename>resource2</quer:resourcename>
This is what i tried so far and each time it always only filters for resource2
on the front end, the user has a powerfilter to filter out for multiple resources and i want to replicate that on the back end. Could you maybe give me an example of how you are using this feature or if there is another way to get to my end goal that you know of, it would be greatly appreciated.
Thanks in advanced,
Malay
Original Message:
Sent: 08-07-2019 02:16 AM
From: Nick Darlington
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
I could be wrong - and I would be happy to be corrected if so - but I don't think the power filters were literally being passed into an NSQL Query soap call's FilterExpression element.
Instead that section is used for putting in a raw 'WHERE' clause condition (without the 'WHERE' keyword), just as if it were SQL.
Although not exactly correct, consider in your NSQL the @FILTER@ expression you have to put in there, and imagine that this is being replaced with your FilterExpression value. Our apps are using that approach daily several times over, but I don't ever remember it having support for the Power Filter style of syntax. Since it's pure SQL injection into that expression though, you can convert any power filter expression into the corresponding SQL and place it there to achieve the same result.
Original Message:
Sent: 08-06-2019 12:48 PM
From: malay thaker
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
Hey do you know if this solution still works? i am trying to do te same task, but i cant get a response that works. What expression are you using? the regular expression or the evaluated expression when looking at the powerfilter tab on the portlet?
Original Message:
Sent: 09-12-2017 04:36 AM
From: Richard Jackson
Subject: How do I use the 'Build Power Filter' option in a SOAP VBA call?
Hi,
I've got a SOAP query which calls data from our Clarity instance but I need to modify the query to include a Power Filter string to return data only for specific items which fall between a range of dates.
I can build the power filter to look like this: nsql.ag_base_dt == dateMacro("ThisWeek") which gives me the items for This Week but I need to have that in a SOAP call.
My SOAP call, currently, looks like this:
'SOAP message.
soapstr = "<soapenv:Envelope xmlns:soapenv=" & Chr(34) & "http://schemas.xmlsoap.org/soap/envelope/" & Chr(34)
soapstr = soapstr & " xmlns:quer=" & Chr(34) & "http://www.niku.com/xog/Query" & Chr(34) & ">"
soapstr = soapstr & "<soapenv:Header>"
soapstr = soapstr & "<quer:Auth>"
soapstr = soapstr & "<quer:SessionID>" & SessionId & "</quer:SessionID>"
soapstr = soapstr & "</quer:Auth>"
soapstr = soapstr & "</soapenv:Header>"
soapstr = soapstr & "<soapenv:Body>"
soapstr = soapstr & "<Query xmlns=" & Chr(34) & "http://www.niku.com/xog/Query" & Chr(34) & ">"
soapstr = soapstr & "<Code>e3_ws_ppr_ms_q</Code>"
soapstr = soapstr & "<Filter>"
soapstr = soapstr & "<param_ppr_id>" & current_ppr_id & "</param_ppr_id>"
soapstr = soapstr & "</Filter>"
soapstr = soapstr & "<Sort>"
soapstr = soapstr & "<Column>"
soapstr = soapstr & "<Name>agreeddate</Name>"
soapstr = soapstr & "<Direction>asc</Direction>"
soapstr = soapstr & "</Column>"
soapstr = soapstr & "</Sort>"
soapstr = soapstr & "</Query>"
soapstr = soapstr & "</soapenv:Body> "
soapstr = soapstr & "</soapenv:Envelope> "
Any help as to how I can include the Power Filter criteria would be greatly appreciated :-)
Thanks