× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. April 2007

Re: Stored Procedure on iSeries.

I removed the semicolon from
IF EXISTS (SELECT * FROM MYDB.EMAILS WHERE EMAIL_ID = em;)

but now it is giving me this 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 "END-OF-STATEMENT" was found following "WHERE
EMAIL_ID = em". Expected tokens may include: "<psm_semicolon>". LINE
NUMBER=13. SQLSTATE=42601

my line number 13 is this(in red):
IF EXISTS (SELECT * FROM OMS.EMAILS WHERE EMAIL_ID = em)
THEN
UPDATE OMS.EMAILS
SET EMAIL_DESC= 'YYY'
WHERE EMAIL_ID = em;
ELSE
INSERT INTO OMS.EMAILS(EMAIL_ID, EMAIL_DESC)
VALUES (em, 'YYY')
END IF

when I tried to take out the semicolon out of the line 13, it is giving me
this 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=13.
SQLSTATE=42601


Thanks for the responses.

Kindest regards,
Misty.



On 4/30/07, jstevens@xxxxxxxxxxxxxx <jstevens@xxxxxxxxxxxxxx> wrote:

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.
>
>
--
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 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 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.



As an Amazon Associate we earn from qualifying purchases.

This thread ...
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.