How to exit rule when SQL record count is 0
Q: We have an SQL as source job that emails the results of the SQL statement as a file. The problem is that we do not want the results/email sent when there are no records. I see that the variable %QSC contains the record count. How can I use that variable to exit the rule and not email the empty results file?
A: So I guess you have SMTP as Destination?
Please add the following SQL ‘On Destination’ script and try once more:
Begin psExitCode:= 1; // ... add your code here If psQSC = 0 Then psExitCode:= 0; End.
- Feedback customer: Yes, it is a SMTP destination. Based off what you sent below I added the Pascal script with the addition of some logging:
This works as expected and what we want, but it also creates the temp file. Is there a way the temp file can be suppressed? If not, I can add a rule to delete them.
8/9/2023 5:07:43 PM RecordCount: 0
8/9/2023 5:07:43 PM FieldDefs: “contributor_system”,”logged_datetime”,”mrn”,”patient_name”,”fin”,”error_status”,”error_text”,”message_text”
8/9/2023 5:07:43 PM Copied Cerner_ESI_log_other_2023-08-09.csv to C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv;110
8/9/2023 5:07:43 PM Backup Successful C:\Users\SVCFIL~1\AppData\Local\Temp\LimagitoX\LimagitoX.165\Cerner_ESI_log_other_2023-08-09.csv to C:\Data\SourceFileBackups\Prod\2023\08\09\Cerner_ESI_log_other_2023-08-09.csv
8/9/2023 5:07:43 PM Compiling..
8/9/2023 5:07:43 PM Compiled Succesfully
8/9/2023 5:07:43 PM psQSC (SQL record count) = 0. Exiting rule.
8/9/2023 5:07:43 PM Total Files Redo, Count: 1 & Size: 110
- Reply to customer:
Please try to add
DeleteFile(psFilePath + psFileName);
to the script.
Begin psExitCode:= 1; // ... add your code here If psQSC = 0 Then Begin DeleteFile(psFilePath + psFileName); psExitCode:= 0; End; End.
It seems you are using our Backup option. In your case, we would add the ‘backup’ as a second WIN destination because otherwise you still get the temporary file in your backup folder (instead of using our BACKUP option).
- Feedback customer: That got it! Thanks you for your help. We are all good now and the rule is working as we want it to.
If you need any info about this ‘exit rule when SQL record count is 0’ question, please let us know.
#sql #filetransfer #filemanagement
Best regards,
Limagito Team
