Database view with list field

Dominik9 · ‎01-03-2023

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

-O- · ‎01-06-2023

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.

View solution in original post

-O- · ‎01-04-2023

You mean this:

Dominik9 · ‎01-04-2023

Hi -O-

Yes, that is exactly what I am looking for. How did you create it?

Thanks

Dominik

-O- · ‎01-05-2023

Here's what I did to display incident work notes list users each on its own row:

and the where clauses in text:

!isnull(inc.work_notes_list)

instr(inc.work_notes_list, usr.sys_id)>0

Dominik9 · ‎01-05-2023

Hello -O-

Thank you for the solution. This is exactly what I was looking for.
Unfortunately this doesn't work in my case and I don't know exactly why yet. I rebuilt your scenario in a PDI and it works perfectly. Then I rebuilt my scenario and I get an error message when I try to open the DB View.

My scenario:

We have a custom table (u_sla) which extends the cmdb_ci table. On this we have a field of type "List" (u_support_groups) with the reference on sys_user_group. If I now create the exact same scenario as in your example in the DB View I get an error message when calling it.

The field name is correct (I checked it multiple times :-D) and the field exists on the table.

Do you have any idea what this could be?

Thanks

Dominik

-O- · ‎01-05-2023

I'd try changing the dot into an underscore in all cases, e.g:

sla.u_support_groups -> sla_u_support_groups

Also I'd have a look into the (error) logs: the actual SQL might be logged and it may be that the error is caused by a totally unrelated situation in fact. Might give a clue as to what exactly to modify to fix the error.