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