Solutions Company Statit Training Home
 



Testing a Query for No Records Returned

Abstract: How to handle No Data Returned gracefully.

Products: Statit e-Server, Statit Custom QC

Q: In my macro, if my query does not return data the macro fails. I need to know if my query returned data before I execute other commands in my macro. How do I test a query for no records returned?

A: There are actually a number of ways to check for no records returned from a query. I will illustrate two in this article.

beginsql/endsql

Perhaps the best is to use the beginsql/endsql syntax. There are some caveats listed below. The basic form of this syntax is:

beginsql
<SQL Statement(s)>
endsql

For example:

dbconnect /dsn="StatitDemoDB"
beginsql
SELECT * FROM box_inspection_Data where Parameter='BX Count'
endsql

beginsql has a couple of options to help us with our problem.

/norecords=<Error Exit Warning>
/norecmessage ="Message String"

For a query that returns no data, the action will depend on the /norecords keyword:

Error: An Error will be generated using the norecmessage (if specified), the macro will stop and no further commands executed.

Exit: The macro will exit and a Warning will be generated. Of importance to Statit e-QC people, a Warning will not be displayed to the results frame. A Warning is output to the session log and the output in Statit e-QC results frame is Procedure Log output (or graphics window output), so the norecmessage does not show up in the Results Frame. However, using this keyword in Statit Custom QC will display a warning message to the user in the session log.

Warning: A Warning message will be generated using the norecmessage and the macro will continue processing. Again, the Statit e-QC output will not see the Warning.

An example:

dbconnect /dsn="StatitDemoDB"
beginsql /norecords=error /norecmessage="No data from Box Inspection"
SELECT * FROM box_inspection_Data where Parameter='BX Cont'
endsql

Because of the misspelling of the Parameter BX Count, no data will be returned and an error will be generated with the message: "No data from Box Inspection".

The beginsql/endsql can be used in Statit e-QC macros and in macros used in Statit Custom QC.

beginsql/endsql can house more than one SQL statement:

dbconnect /dsn="StatitDemoDB"
beginsql /norecords=error /norecmessage="No data from Box Inspection"
SELECT * FROM box_inspection_Data where Parameter='BX Count';
SELECT * FROM Specs where parameter='BX Count'
endsql

Notice the use of the semicolon at the end of the SQL statement. Here the semicolon is not a line continuation character, but marks the end of an SQL statement. In this case, the /norecords option applies to any data retrieved from the two statements. If either of the SQL statements returns records then the /norecords option does not apply. The first query would not return data but the second would, so no error message would be returned.

Alternative Method Checking $db_numrecs

In procs (and in older versions of macros) you may be using either the dbsql command or the dbsql() function. You can check for the number of records returned from a query by checking the value of $db_numrecs. This temporary variable is updated by a query with the number of records retrieved in the query.

An example:

dbconnect /dsn="StatitDemoDB"
dbsql SELECT * FROM box_inspection_Data where Parameter='BX Cont'
ifx $db_numrecs == 0 then
goto fatalerror
endif

If no records are retrieved in the query, the control will pass to the fatalerror label.

In Statit e-QC, an even more user-friendly method is to inform the user of the issue:

if $db_numrecs = 0 then
beginHTML
<center>
<h1>No Data for Product Characteristic: {%Test%}</h1>
<h3>Test Machine: @v[%Machine] </h3>
<h2>Date: @v[%Date]</h2></center>
endhtml
endif