topic Either [ ] or ` `. Basic SQLâ€¦ in Product Forum

Filter Collection

savanpatel — Mon, 10 Dec 2018 21:17:00 GMT

I am having issue filtering a collection that has a column name with space. I get the following error: Internal : Could not execute code stage because exception thrown by code stage: Syntax error: Missing operand after 'Name' operator. I have attached picture for reference.

Try enclosing First Name in…

AmiBarrett — Mon, 10 Dec 2018 21:25:00 GMT

Try enclosing First Name in single quotes as well.

Using single quotes does not…

savanpatel — Mon, 10 Dec 2018 21:27:00 GMT

Using single quotes does not work, it does get rid of the error message but it does not give the right output result.

Try making a new action with…

AmiBarrett — Mon, 10 Dec 2018 21:45:00 GMT

Try making a new action with this as the code stage. I replaced that action with this one for ease of use long ago.

Inputs:
Collection - Collection
FilterString - Text
Column - Text

Outputs:
Sorted Collection - Text

String filter = """";
if(FilterString!="""") 
{
	filter = ""`""+Column+""`"" + "" like '%""+FilterString+""%'"";
}
else
{
	filter = ""`""+Column+""`"" + "" = ''"";
}
DataView dv = Collection.DefaultView;
dv.RowFilter = filter;
Sorted_Collection = dv.ToTable();

Hello, Try using in this…

SukeshManthena — Mon, 07 Jan 2019 17:19:00 GMT

Hello, Try using in this format in Filter Attribute in ""Utility - Collection Manipulation"" , as Coll1.First Name = ""White Shark"" and check it. Good Luck.

Enclose that column nameâ€¦

sai_bhargavaraj — Fri, 03 May 2019 10:09:00 GMT

Enclose that column name within square brackets.

I had this problem manyâ€¦

ChristianLobosc — Sun, 05 May 2019 23:57:00 GMT

I had this problem many times until I found a simple and easy solution. When filtering with VB.net codes the column name is typically put in brackets. "[Column Name] LIKE 'text you want to filter'" The [ ] will allow you to use a space. Make sure Quotation marks are used before the [ as blue prism will think you are referring to a data item if you don't. Â

Either [ ] or ` `. Basic SQLâ€¦

AmiBarrett — Mon, 06 May 2019 19:37:00 GMT

Either [ ] or ` `. Basic SQL notation.

RE: Either [ ] or ` `. Basic SQLâ€¦

PavelKrál — Wed, 10 Jul 2019 07:51:00 GMT

Hi all,

any Idea why ? wildcard is not working?

eg.: [Area] like '04? AB'

------------------------------
Pavel Král
RPA Specialist Senior
MANN+HUMMEL Service s.r.o
Europe/Prague
------------------------------

RE: Either [ ] or ` `. Basic SQLâ€¦

david.l.morris — Wed, 10 Jul 2019 10:46:00 GMT

I guess I haven't ever tried using a wildcard in the middle of a filter expression. You'd think I'd need that at some point. Go figure. 😃

Blue Prism is using the Select method on the datatable to filter the results (that is, if you go all the way into the code stage), and it looks like the Select method only supports wildcards at the beginning or end of the text such as '*this*'. I suppose the other issue here is that the Select method doesn't seem to support ? as a wildcard. I haven't looked into whether there's another single character to use instead, but at least when used in Blue Prism, the ? is read as part of the string.

You may want to use LINQ instead. It seems to be the go-to when performing queries on datatables (collections) in Blue Prism beyond what the existing actions provide.

------------------------------
Dave Morris
3Ci @ Southern Company
Atlanta, GA
------------------------------

RE: Either [ ] or ` `. Basic SQLâ€¦

PavelKrál — Wed, 10 Jul 2019 11:01:00 GMT

I was just thinkig it has same possibilities like show on this page: https://www.w3schools.com/sql/sql_wildcards.asp

i have very small knowledge about SQL, but i will try to find some solution. I will check the LINQ. It will come handy to have a better possibilites for filtering. At least in my projects 🙂

Thanks for your reply

------------------------------
Pavel KrÃ¡l
RPA Specialist
MANN+HUMMEL Service s.r.o
------------------------------

RE: Either [ ] or ` `. Basic SQLâ€¦

AmiBarrett — Wed, 10 Jul 2019 16:56:00 GMT

You could always use another code stage to execute a custom query against it.

Inputs:
Collection - Collection
Query - Text

Outputs:
Sorted Collection - Collection

DataTable temp = Collection.Clone();
DataRow[] dr = Collection.Select(Query);
foreach(DataRow row in dr)
{
	temp.ImportRow(row);
}
Sorted_Collection = temp;

------------------------------
Ami Barrett
Lead RPA Software Developer
Solai & Cameron
America/Chicago
------------------------------

RE: Either [ ] or ` `. Basic SQLâ€¦

AndreyKudinov — Wed, 14 Aug 2019 10:37:00 GMT

Because DataTable is not a real database and wildcards in the middle just doesn't work as documented.
You can use something like this sometimes: [Area] like '04%' and [Area] like '% AB'
But keep in mind this is not the same thing.

Custom action filtering rows one by one with regex condition would work.

------------------------------
Andrey Kudinov
Project Manager
MobileTelesystems PJSC
Europe/Moscow
------------------------------