Reporting on large lists
You might need to query against a large list; for example, you have been given a list of VIDs and you need to generate a report extracting details only for that list of VIDs. Examples of large lists include VIDs, reference codes, ZIP/postal codes, and so on. However, when you query a large list (more than 5000 values ) errors can occur because the query times out. To avoid this issue, add your data to a lookup table and create a query against the table.
Large list query error
When you add a large list to an IN condition or NOT IN condition within the SQL query editor, the syntax can be valid but errors will occur because the query takes too long to build an execution plan. This means that the Run Query button cannot be actioned.
Query a lookup table
Lookup tables can contain very large lists of data. When you query against a lookup table, the query will build an execution plan and you can run the query without errors.
Add the VIDs to a one column .csv file. The file must have a header.
Tip: Create a table with a generic name. You can re-upload the table with new data each time you need to query a large list. If you choose to re-load the lookup table with a new data set, ensure this lookup table is not being used in other parts of the application (NEX rules or Saved Reports).
Use the .csv file to create a lookup table in your Network instance.
When the table is created, it is immediately available to report on.
- On the Network menu bar, click Reports > SQL Query Editor.
In the tree view in the left pane, expand the Lookup Tables category and find the lookup table name; for example,
large_list_of_hcp_vids__t. Lookup tables have the
For tips about using the tree view to create your query, see Reporting tree view.
Create your SQL query using the lookup table. You can use an IN condition in a WHERE condition, or you can use a JOIN.
Example - IN condition
Find HCP status for my list of VIDs.
SELECT vid__v, hcp_status__v FROM hcp WHERE vid__v IN ( SELECT hcp_vid FROM large_list_of_hcp_vids__t )
Example - JOIN
Find HCP status for my list of VIDs
SELECT hcp.vid__v, hcp.hcp_status__v FROM hcp INNER JOIN large_list_of_hcp_vids__t ON hcp.vid__v = large_list_of_hcp_vids__t.hcp_vid
When the query is valid, click Run Query. The report results will display.