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.

  1. 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).

    Example

  2. 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.

  3. On the Network menu bar, click Reports > SQL Query Editor.

  4. 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 __t suffix.

    For tips about using the tree view to create your query, see Reporting tree view.

  5. 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.