How to deal with SQL Query when there are no results

[Graham]

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.

[RezaDorrani]

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

[v-bacao-msft]

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,

[Graham]

This is the error from flow;

This is the content;

The formula is: 

length(body('Get_List_of_Funds_Requests')?['value'])

[v-bacao-msft]

Hi @Graham ,

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

Best Regards,

[Graham]

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.

[v-bacao-msft]

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,

[Graham]

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.