RE: How to modify a stored procedure

CREATE PROCEDURE FOXWELL/DOAATN001 (
IN CLIENT_NO NUMERIC(9, 0) )
language sql
begin
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;
end

Hth, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: How to modify a stored procedure

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