Hi!
Is it possible to look for cases with no tasks in SQL maintenance?
We have a report for this already that uses database fixed and a maths field that runs SQL query to look for those.
But I wonder if something similar is possible in SQL Maintenance Criteria section or maybe some workarounds as previous two methods are not possible there(I might be wrong though!)
Thanks!
No Tasks SQL Criteria
Re: No Tasks SQL Criteria
Hi,
I've had to do something similar - best thing to do I found was a count of the tasks flagged on the file by creating a maths item like below:
vara = FETCH( "FetchType,COUNT" , "Diary" , "action_code" , "FOR EACH Diary NO-LOCK WHERE Diary.case_no = '" + {case.key} + "' AND status_flag = YES" )
PUT( vara , {Tasks on Diary.Number} )
If you're ever looking for specific tasks, just add in AND action_code = "Your LA Code"
I use a report for it but you can just have the maths item in an SQL and remove the PUT and just return the number as the maths field.
I've had to do something similar - best thing to do I found was a count of the tasks flagged on the file by creating a maths item like below:
vara = FETCH( "FetchType,COUNT" , "Diary" , "action_code" , "FOR EACH Diary NO-LOCK WHERE Diary.case_no = '" + {case.key} + "' AND status_flag = YES" )
PUT( vara , {Tasks on Diary.Number} )
If you're ever looking for specific tasks, just add in AND action_code = "Your LA Code"
I use a report for it but you can just have the maths item in an SQL and remove the PUT and just return the number as the maths field.
Last edited by aadrisc on Tue Aug 04, 2026 1:30 pm, edited 1 time in total.
Re: No Tasks SQL Criteria
Hi aadrisc,
Thank you for your reply.
I don't think Proclaim supports maths items as criteria/selection tests in SQL Maintenance.
I also tried a workaround by reversing the comparison. Since maths items can't be selected as the first comparison item, I created a constant field (AlwaysZero) with an initial value of 0 and used Task Count as the second comparison item. This resulted in:
Claims Link.AlwaysZero.Number is Equal To the Data in the Merge Field "Claims Link.M Task Count.Number"
However, it still returns 0 results.
Do you know if there's another way to achieve this or am I doing something wrong?
Thank you for your reply.
I don't think Proclaim supports maths items as criteria/selection tests in SQL Maintenance.
I also tried a workaround by reversing the comparison. Since maths items can't be selected as the first comparison item, I created a constant field (AlwaysZero) with an initial value of 0 and used Task Count as the second comparison item. This resulted in:
Claims Link.AlwaysZero.Number is Equal To the Data in the Merge Field "Claims Link.M Task Count.Number"
However, it still returns 0 results.
Do you know if there's another way to achieve this or am I doing something wrong?
Re: No Tasks SQL Criteria
Hi,
For the first time run, I would honestly run an SQL on ALL files (so no criteria) with your maths item as a column. Inside that maths item, if you then create a field "Task Count" and PUT the result of the maths item in it, it will populate on this run of the SQL (just export and filter down for the right ones).
Once it's been ran once then you can just have criteria of "Task Count.Number" IS EQUAL TO (the fixed value entered here) 0 on the next run.
I'd also have the above maths item with the FETCH in running on the exit case linked action just so it updates everytime someone leaves a file - means if you ever need to do this in the future it's all updated and ready.
This is how I set it up and have continued to report off it and it works well.
Let me know if that makes sense
For the first time run, I would honestly run an SQL on ALL files (so no criteria) with your maths item as a column. Inside that maths item, if you then create a field "Task Count" and PUT the result of the maths item in it, it will populate on this run of the SQL (just export and filter down for the right ones).
Once it's been ran once then you can just have criteria of "Task Count.Number" IS EQUAL TO (the fixed value entered here) 0 on the next run.
I'd also have the above maths item with the FETCH in running on the exit case linked action just so it updates everytime someone leaves a file - means if you ever need to do this in the future it's all updated and ready.
This is how I set it up and have continued to report off it and it works well.
Let me know if that makes sense
