Please provide Dataset Watchdog Custom Query examples on documentation page

Options
vam
vam Member

Hello,

I'm trying to create an email report using Dataset Watchdog. I'd like it to output the results of a custom query to an email, sent out on a periodic basis.

There are no examples to be found on Domo Support or on the Community Forums for how to structure such a query, or what the limitations of this SQL prompt are.

The Support page references the ability to make custom queries, but provides no examples thereof: (https://domo-support.domo.com/minasan/s/article/000005105?language=en_US)

There is also this post from last year on the Community Forum (https://community-forums.domo.com/main/discussion/67089/data-watchdog-adding-example-queries), which recognizes the problem, but that's it.

The empty prompt itself offers a simple SQL query to work by with generic nomenclature ('select * from table where column = condition', but it doesn't mention how to reference a table to pull from. Do we use the Dataset ID from the Domo Governance Tables? The name from those tables?

Not to mention other relevant questions, for example: How are column names containing spaces handled? Which functions can this SQL prompt handle?

Some examples added to the documentation page would be a fairly light lift, and would make this functionality extremely useful to all.

Thank you.

Answers

  • Interesting….yes, it appears that Domo should make some additions to the support page.

    I would assume the queries have a single dataset limitation. Domo dataflows use MySQL syntax, so try that rather than MS SQL syntax. Use backtick as common practice for calling out datasets or fields. I recall that working for me. I've seen posts mentioned joins failing. That's why I think you have to stick with a single dataset. I also don't think you can use any kind of function in the query.

    Try things like

    SELECT *
    FROM `Customer Records`
    WHERE `Email` IS NULL OR `Phone` IS NULL
    SELECT `Product`, SUM(`Sales`) AS `Total Sales`
    FROM `Weekly Sales`
    WHERE `Date` >= CURRENT_DATE - INTERVAL 7 DAY
    GROUP BY `Product`
    ORDER BY `Total Sales` DESC
    LIMIT 10 SELECT * FROM `12345678-90ab-cdef-1234-567890abcdef`

    In that last example, this would be if you can call the dataset from it's id rather than its name. Grab the id from the URL when you look at a dataset.

    ** Was this post helpful? Click Agree or Like below. **
    ** Did this solve your problem? Accept it as a solution! **