Re: How to modify a stored procedure

Again I've never used STRSQL to create a procedure, I've always used
run iNav's Run SQL Scripts or had the statement in a source member...

But it'd seem to me that while STRSQL doesn't want the final ; it'd
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

As far as iNav....

Have you tried adding your own SET PATH statement to the procedure?

In any event, while I can see creating a procedure using iNav the
first time, I'd never use it to modify (ie. recreate an existing).
I'd generate the source statements and work with those from then on.

Charles


On Fri, Jun 12, 2009 at 4:38 AM, David FOXWELL<David.FOXWELL@xxxxxxxxx> wrote:

Subject: Re: How to modify a stored procedure
From: Charles Wilt <charles.wilt@xxxxxxxxx>

what "syntax problems" did you run into using STRSQL?

Charles,

This is what I've pasted into my SQL session from inav :

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


I get the message "Keyword ELSE misplaced"

I could settle for the procedure as created in iNav, but it inserts a SET PATH command so that the table always has to be in my library. How do I get it to execute on another file in another library?

Thanks
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.