Thanks, Chuck
The point about requiring ELSE supports good practice is well-taken. In one of my procedures, in fact, I set a "found" variable to 0 in the ELSE - I'm rotating through 4 different possible locations for a phone number, creating a SELECT statement string that I use to prepare a statement and declare a cursor. It is appropriate here to do as you suggest - I even have a LEAVE in the ELSE.
The other procedure calls QCMDEXC to run a QShell function that returns Active Directory attributes as name-value pairs. I need to flatten them into rows. I have asked for exactly 7 attributes, not all are returned for each AD name, and there is a CN= pair, as well as a blank row between each group.
The only one that doesn't matter to me is the blank row - I use the CN= as a level-break of sorts. I do have an else here, which really handles the blank row. I suppose ITERATE could take care of me here. But I think not - I have a FETCH right after the CASE, within a WHILE loop, and that would be skipped with an ITERATE.
Putting the CASE in a compound statement - that would work nicely, too. It fits my structure nicely, I think.
Maybe a hint at the flow will help - some declarations & other details are omitted
/* Variables */
DECLARE AT_END INT DEFAULT 0;
/* Cursors */
/* Get Active Directory attributes */
DECLARE C1 CURSOR FOR S1;
/* Handlers */
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET AT_END = 1;
/* Global temporary table for LDAP attributes */
DECLARE GLOBAL TEMPORARY TABLE SESSION/LDAPATTRS
(ldapattrs CHAR(100));
/* Pull attributes into temp table */
set sqlStmt = 'select * from qtemp/ldapattrs';
prepare s1 from sqlStmt;
OPEN C1;
FETCH C1 INTO ldaptext;
/* Walk through specialist IDs */
WHILE AT_END = 0 DO
case
-- several WHENs that each set a variable from a name-value pair
-- the variable is used later in an INSERT when a CN= is encountered
else
set at_end = at_end; /* Dummy operation */
end case;
/* Get next ID */
FETCH C1 INTO ldaptext;
END WHILE;
----- Original Message -----
On 14 Apr 2013 16:05, Vernon Hamberg wrote:
<<SNIP>>
1. When using the CASE control statement, I find that it requires
an ELSE in this context. Now many times I don't need the ELSE - all
conditions are covered in the WHEN clauses, and I don't want some
"just anything" bit.
The docs have this information -
"If none of the conditions specified in the WHEN are true, and an
ELSE clause is not specified, an error is issued at runtime, and
the execution of the CASE statement is terminated (SQLSTATE 20000)."
So any suggestions for a NOOP? I've used a SET of a variable to
itself. I suppose I could create a CONTINUE HANDLER.
<<SNIP>>
IMO a requirement for an ELSE enforces a good programming habit; i.e.
any failed assumptions should always be handled or notified. If the
assumption is that every known condition would be handled by the WHEN
clauses coded prior, then the ELSE should be coded with something like
the following statement, to inform that the assumption was incorrect;
such is often referred to in computing as an "assertion":
SIGNAL sqlstate value 'UC001' set message_text='Unexpected case';
Instead of SIGNAL, a CALL could be made to a routine that dumps
information about the failed assumption; probably passed as a literal
string specified in the source code; e.g.:
CALL ASSUME ('stCount value should only =1 or =2');
However if choosing to use SET, then rather than set any variable to
itself, I would declare a variable, perhaps named NOOP, and set that
variable to any compatible value whenever\wherever I wanted the
null\empty statement. This might be what was described as "just
anything", but I could make no sense of what was meant. e.g.:
case stCount
when 1 then set wc = 'One' ;
when 0 then set wc = 'Two' ;
else set NOOP = 0 ; /* trivial work; token NOOP appears */
end case ;
Otherwise there are GOTO, LEAVE, and possibly ITERATE or RETURN
control statements, depending on the context.
To use a GOTO to get after the CASE, requires that some other
statement follows the label; e.g.:
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else goto stCaseEnd ; /* effectively a NOOP */
end case ;
stCaseEnd:
/* some statement is required here; i.e. can not be an "end" */
If necessary, the CASE may be set within its own compound statement
to accomplish a LEAVE to just after the CASE, if leaving the routine is
not desirable by LEAVE procname or RETURN integer; e.g.:
stCase:
begin
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else LEAVE stCase ; /* effectively a NOOP */
end case ;
end stCase ; /* LEAVE jumps to here */
2. Speaking of which - it seems that a HANDLER also requires a
procedure statement - a NOOP would be nice sometimes - this would
give a way to ignore certain "errors", like table already exists
on a CREATE TABLE <<SNIP>>
The SQL procedure statement noted in the syntax diagram for the
handler can be a compound statement [as later comments acknowledge;
though they are snipped]. In my experience the compound statement can
be /empty/ such that there is just a BEGIN and END.
Thinking on that... I tried the same for the ELSE, and the same form
of an /empty/ request functioned for the ELSE as well; just as it does
for a condition handler. Maybe not as nice as a NOOP or a just a semicolon:
case stCount
when 1 then set wc = 'One' ;
when 2 then set wc = 'Two' ;
else begin end ; /* empty request */
end case ;
midrange.com
