Re: How to modify a stored procedure

[CRPence <CRPbottle@xxxxxxxxx>]

David FOXWELL wrote:
> Charles Wilt wrote:
> > But it'd seem to me that while STRSQL doesn't want the final
> > semicolon ; it would still need any in the middle...
> >
> > CREATE PROCEDURE FOXWELL/DOAATN001 
> > (     IN CLIENT_NO NUMERIC(9, 0)  )
> >    CASE
> >        WHEN CLIENT_NO <> 0
> >            THEN
> >                  UPDATE DOAATN SET STRTFL = 'N'
> >                       , SMAJHT = CURRENT TIME
> >                   WHERE SCLTNO = CLIENT_NO;
> >            ELSE
> >                  UPDATE DOAATN SET STRTFL = 'N'
> >                       , SMAJHT = CURRENT TIME;
> >    END CASE
>
> Now it's saying that CASE is misplaced. If I take off the CASE and
> just have the UPDATE, then UPDATE is misplaced.  Ok, Friday
> afternoon's not the best time to try. Try again Monday morning!

  Besides having removed more than the last semicolon in the first 
attempt, there appears to have been excessive trimming of that 
source for the later attempts.  Most important, at a minimum, the 
LANGUAGE SQL is required before the CASE.  Try the following:

<code>

 CREATE PROCEDURE FOXWELL/MYPROC
  ( IN CLIENT_NO NUMERIC(9, 0) )
   LANGUAGE SQL
   SPECIFIC FOXWELL/MYPROC
   NOT DETERMINISTIC
   MODIFIES SQL DATA
   CALLED ON NULL INPUT
   SET OPTION ALWBLK = *ALLREAD
            , ALWCPYDTA = *OPTIMIZE
            , COMMIT = *NONE
            , DECRESULT = (31, 31, 00)
            , DFTRDBCOL = *NONE
            , DYNDFTCOL = *NO
            , DYNUSRPRF = *USER
            , SRTSEQ = *HEX
 CASE
  WHEN CLIENT_NO <> 0
  THEN UPDATE MYFILE SET STRTFL = 'N'
                       , SMAJHT= CURRENT TIME
         WHERE SCLTNO = CLIENT_NO
       ;
  ELSE UPDATE MYFILE SET STRTFL = 'N'
                       , SMAJHT= CURRENT TIME
       ;
 END CASE

</code>

  In the RUNSQLSTM source and Run SQL from iNav, a semicolon can 
appear following the statement _even though_ it is syntactically 
incorrect, *only* because they are both script processors.  For 
those interfaces, the semicolon suggests the data following the 
final semicolon is another statement; but the EOF indicates the last 
statement is effectively an empty string, it is just ignored.  The 
STRSQL interpreter supports only one statement at a time, so the 
superfluous semicolon is flagged as an error.

Regards, Chuck