RE: Stored Procedure on iSeries.

[jstevens@xxxxxxxxxxxxxx]

Is the semi-colon required at the end of the IF EXISTS (SELECT * FROM 
MYDB.EMAIL WHERE EMAIL_ID = em;) statement?


Jeff Stevens
Mize, Houser & Co. P.A.
913 451 1882
JStevens@xxxxxxxxxxxxxx





"Phil Kestenbaum" <pkestenbaum@xxxxxxxxxxx> 
Sent by: rpg400-l-bounces@xxxxxxxxxxxx
04/30/2007 02:34 PM
Please respond to
RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx>


To
"RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
cc

Subject
RE: Stored Procedure on iSeries.






Yes my mistake I wonder if it needs parens?

-----Original Message-----
From: rpg400-l-bounces+pkestenbaum=sditech.com@xxxxxxxxxxxx
[mailto:rpg400-l-bounces+pkestenbaum=sditech.com@xxxxxxxxxxxx] On Behalf
Of Misty Thornton
Sent: Monday, April 30, 2007 3:34 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Stored Procedure on iSeries.

Thanks for the response. My update statement is already in place, right?

Kindest regards,
Misty.


On 4/30/07, Phil Kestenbaum <pkestenbaum@xxxxxxxxxxx> wrote:
> I believe that you need to  code an update statement as well.
>
> -----Original Message-----
> From: rpg400-l-bounces@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Misty Thornton
> Sent: Monday, April 30, 2007 3:20 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Stored Procedure on iSeries.
>
> Hey All;
>
> I am trying to create a stored procedure which will insert a record
into
> a
> file if the record does not exist and updates the record if the record
> already exists. I am only passing one parameter to the stored
procedure
> VARCHAR(10). My EMAILS file has only two fields, EMAIL_ID varchar(10)
> and
> EMAIL_DESC varchar(25). This is how I am trying to code but I keep
> getting
> the following error.
>
> ***********
>
> DB21034E  The command was processed as an SQL statement because it was
> not a
>
> valid Command Line Processor command.  During SQL processing it
> returned:
> SQL0104N  An unexpected token ";" was found following "E WHERE
EMAIL_ID
> =
> em".
> Expected tokens may include:  "<space>".  LINE NUMBER=7.
SQLSTATE=42601
> ***********
>
>
>
> CREATE PROCEDURE MYDB.MANAGE_EMAILS
>
> ( IN em VARCHAR(10))
> SPECIFIC MYDB.MANAGE_EMAILS
>
> DYNAMIC RESULT SETS 1
>
> P1: BEGIN
>        IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em;)
>          THEN
>             UPDATE MYDB.EMAIL
>               SET  EMAIL_DESC= 'YYY'
>               WHERE EMAIL_ID = em;
>           ELSE
>             INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
>               VALUES (em, 'YYY')
>        END IF;
>      ELSE
>        UPDATE MYDB.EMAIL
>
>            SET EMAIL_DESC = 'YYY'
>            WHERE EMAIL_ID = em;
>    END IF
> END P1
>
>
>
> It almost seems like I am missing something really small here. Any
help
> in
> the right direction is much appreciated. TIA.
>
> Kindest regards,
>
> -Misty.
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
> list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.
>
>
> This message contains information proprietary to our company. It is
> intended to be read only by the individual or entity named above or
their
> designee.
> Any distribution of this message or the information contained herein
> without written permission from our company is strictly prohibited. If
the
> reader of this message is not the intended recipient or an agent
responsible
> for delivering it to the intended recipient, you are hereby notified
that
> you have received this document in error and that any review,
dissemination,
> distribution, or copying of this message is strictly prohibited. If
you have
> received this communication in error, please notify us immediately by
> e-mail, and delete the original message.
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing
list
> To post a message email: RPG400-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
> or email: RPG400-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/rpg400-l.