Database view with list field

[Dominik9]

Hi Experts

We have a table (Table A) in which support groups are referenced in a list field. To be able to make proper evaluations we had the idea to create a database view. We want a DB View in which one entry is created for each support group from the list field. An example how we imagined this:

Example table A has 1 record with 4 support groups. The DB View would then have 4 rows as follows:

Record 1; Support Group 1;
Record 1; Support Group 2;
Record 1; Support Group 3;
Record 1; Support Group 4;

Unfortunately in DB Views "CONTAINS" and "LIKE" is not supported in the Where clause. However, I found an article in which this is done in a BR (Solved: Database View - List Field - ServiceNow Community). The problem is that in our case around 250'000 records are first created and would then be filtered by the Business Rule. This obviously has a huge performance impact which we would like to prevent.

Does anyone have an idea how we can solve this? Is the DB View even the right approach?

Regards

Dominik

[Dominik9]

The following error is generated: "WhereClause - invalid token: 26: no thrown error".
I then found this article (Error on Database VIew "*** ERROR *** WhereClause - invalid token" or "Syntax Error or Access Rule V...) which also says that you need to use an underscore instead of a dot. I did that. Now I get the following error message: "Where clause in view u_dbv2 has an improperly formatted name (instr)". In the logs still the same error "WhereClause - invalid token: 26: no thrown error".

[-O-]

The article gives me an idea: what if you used the "real"/db name of the field in sla.u_support_groups? Go to sys_storage_alias_list.do and look up your field by table and column name than use what you find in Storage alias for the field. That is in place of

sla.u_support_groups

you would write something like

sla.a_str_1 

[-O- · Accepted solution]

Yepp, that does it - just did a try. Here's the view:

and the result:

The only problem I see with this is that the alias might be different in another instance. I mean after moving the Update Sets up the chain, you would have to adjust the view after commit, to match the "local" real DB name.

And yes, if I use the alias "u_products" in the view (vs. the real/DB name "a_str_11") I get the same error as you did.

[Dominik9]

You are a legend - that worked! Thank you so much for your help!

Best regards

Dominik

[-O-]

You're most welcome and thanks for marking the solution!