Unique Or Distinct Record In Veeva Query Language (VQL)

Question

We need to retrieve distinct Product Family (product__v) records that have at least one associated content plan (edl__v).

When executing the following query, duplicate product families are returned for those with multiple associated content plans:

SELECT product__vr.id FROM edl__v WHERE parent__v IS NULL AND product__vr.name__v IS NOT NULL

To ensure unique product families, I attempted to use a subquery in the following query:

SELECT id, name__v, status__v FROM product__v WHERE id IN (SELECT product__v FROM edl__v)

However, I encountered an issue:

{
"responseStatus": "FAILURE",
"errors": [
    {
        "type": "INVALID_DATA",
        "message": "Unknown relationship [edl__v__vr]"
    }
]

}

In SQL, we can use the DISTINCT keyword to retrieve unique records, but I couldn't find a similar option in VQL. Is there any alternative method to achieve this in VQL?

Answer 1

We can use below queries to get Product and Application for content plan Veeva.

SELECT id, name__v,modified_by__v, status__v,modified_date__v FROM product__v WHERE id IN (SELECT id FROM content_plans__vr)

SELECT id, name__v FROM application__v WHERE id IN (SELECT id FROM content_plans__rimr) AND product__v = '<product_id>'

It will return unique result