Please provide Dataset Watchdog Custom Query examples on documentation page

vam

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.

ArborRose

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.