cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Graham
Kudo Collector
Kudo Collector

How to deal with SQL Query when there are no results

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.

7 REPLIES 7
RezaDorrani
Dual Super User II
Dual Super User II

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
Community Support
Community Support

 

Hi @Graham ,

 

Could you provide screenshot of Flow configuration?

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

152.PNG

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

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

This is the error from flow;

Flow1.png

 

This is the content;

Flow2.png

The formula is: 

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

 

Hi @Graham ,

 

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

 

Best Regards,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

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,

Community Support Team _ Barry
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors
Users online (2,759)