Re: Stored Procedure on iSeries.

["Misty Thornton" <misty.thornton7@xxxxxxxxx>]

I have tried this but still getting errors..


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
END P1

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 "ELSE" was found following "WHERE EMAIL_ID =
em
".  Expected tokens may include:  "<psm_semicolon>".  LINE NUMBER=11.
SQLSTATE=42601


Hmm...what am I missing here....:).
Thanks for all the input. Still trying...:(.

Kindest regards,
Misty.






On 4/30/07, qsrvbas@xxxxxxxxxxxx <qsrvbas@xxxxxxxxxxxx> wrote:
> (Apology for messing up the previous Subject...)
>
> rpg400-l-request@xxxxxxxxxxxx wrote:
>
> >    1. Stored Procedure on iSeries. (Misty Thornton)
> >
> > 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
>
> Try this:
>
> P1: BEGIN
>         IF EXISTS (SELECT * FROM MYDB.EMAIL WHERE EMAIL_ID = em)
> <---- Removed semi-colon
>           THEN
>              UPDATE MYDB.EMAIL
>                SET  EMAIL_DESC= 'YYY'
>                WHERE EMAIL_ID = em; <---- Keep semi-colon
>            ELSE
>              INSERT INTO MYDB.EMAIL(EMAIL_ID, EMAIL_DESC)
>                VALUES (em, 'YYY') ; <---- New semi-colon
>         END IF; <---- Keep semi-colon
> --      ELSE                          <-- Commented
> --        UPDATE MYDB.EMAIL           <-- Commented
> --            SET EMAIL_DESC = 'YYY'  <-- Commented
> --            WHERE EMAIL_ID = em;    <-- Commented
> --    END IF                          <-- Commented
> END P1 ;  <---- New semi-colon
>
> Tom Liotta
>
> --
> Tom Liotta
> The PowerTech Group, Inc.
> 19426 68th Avenue South
> Kent, WA 98032
> Phone  253-872-7788 x313
>        253-479-1416
> Fax    253-872-7904
> http://www.powertech.com
> --
> 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.