Unanswered

How to deal with SQL Query when there are no results

(0) Share

Report

Posted on by Graham

119

I have a need to email a weekly list of new items added to a table in SQL. I have written a query in flow but some weeks there are no new items, which causes the flow the fail.

How can I add a condition which sends the query results if there are rows returned, but send a different email when the query result is null? I have tried length(body('Get_items')?['value']) greater than 0, but this fails when the query is null.

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (0)

RezaDorrani 12,143 on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

Hi @Graham

just tried with length(body('Get_items')?['value']) and it worked

can you post screenshot of output from sql query when flow fails

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

Hi @Graham ,

Could you provide screenshot of Flow configuration?

If you are using Get rows action, please refer to the following expression:

In the length() function we need to configure the name of the actual action and use an underscore instead of space.

Best Regards,

Was this reply helpful? Yes No
Graham 119 on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

This is the error from flow;

This is the content;
The formula is:
length(body('Get_List_of_Funds_Requests')?['value'])

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

Hi @Graham ,

Have you seen the execution results of Get List of Funds Requests action?

Best Regards,

Was this reply helpful? Yes No
Graham 119 on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

The query results in no records being fetched, hence the 'Null'. That is what I expected, as this relates to a weekly flow that looks for records added during the previous weeks. Some weeks there will be no new records, so I need flow to do something else in the knowledge there are no results.

Was this reply helpful? Yes No
v-bacao-msft on at

Like (0)

Report

Re: How to deal with SQL Query when there are no results

Hi @Graham ,

Could you provide a screenshot of Get List of Funds Requests action?

We need to provide a viable expression based on the specific execution results.

Best Regards,

Was this reply helpful? Yes No
Graham 119 on at

Like (2)

Report

Re: How to deal with SQL Query when there are no results

Hi,

It is a simple SELECT query - I would rather not share the exact contents, for obvious reasons, but here is something similar;

SELECT
dbo.Incidents.IncidentID,
dbo.Incidents.Reference,
dbo.Funds.RequestID,
dbo.Funds.RequestAmount,
FORMAT(dbo.Funds.RequestDate, 'dd/MM/yyyy', 'en-us') as FundsReqDate
FROM
dbo.Incidents INNER JOIN dbo.Funds ON dbo.Incidents.IncidentID=dbo.Funds.IncidentNumber INNER JOIN dbo.Timelapse ON dbo.Funds.RequestDate>dbo.Timelapse.Weekago

Timelapse is a 'View' on SQL server and calculates the date of -7 days - It's something we use for a lot of queries.

So when no new records are found for the last 7 days, it returns 'Null' to Flow, which breaks the condition statement.

Was this reply helpful? Yes No