In some situations it is more convenient to return a bad SQLCODE as the result of a SELECT instead of the usual SQLCODE 100. This is especially true when using programs like DSNTIAUL and DSNTEP2. These programs give a return code of 8 to JES when a SQL statement fails with a negative SQLCODE. Giving return code 8 when zero rows are selected makes it possible to execute other steps in JCL than you would normally do.
How do you construct a SELECT statement that returns a negative SQLCODE when the number of rows is known? There may be several ways to acheive this, so here is one of them:
Assuming table MY.EMPTY_TABLE is empty the above SELECT will return a negative SQLCODE. If one or more rows is present in MY.EMPTY_TABLE the SELECT returns one row containing an empty string. If you want to have a negative SQLCODE when the table is non-empty you just change about the THEN part and the ELSE part.
Normally you want to know whether a table contains rows fulfilling a certain critria or not. Then all you have to do is to use a WHERE clause in the SQL statement above. If you want to see the contens of rows returned or just know the exact number of rows then you have to make some extra functionality to acheive this.