RE: Embedded SQL exists?

["Whiteman, Greg" <Greg.Whiteman@xxxxxxxxxxxxxxxx>]

I'm sorry! I was sorrily mistaken! You could use this technique for using
sql to update, delete or select, but not insert.

The other method you could use is setup a unique key on EMDFIL, EMDCMP,
EMDFAC, EMDUSR. Don't execute your current select clause. Just run your
insert statement. You'll need to handle the sqlcode return value of -803 on
the insert. If that is returned, then it already exists.

Greg Whiteman
(214) 571-6956
Pager (800)759-8352(#1162882)

greg.whiteman@transamerica.com <mailto:greg.whiteman@transamerica.com>



-----Original Message-----
From: darren@dekko.com [mailto:darren@dekko.com]
Sent: Wednesday, November 28, 2001 12:44 PM
To: rpg400-l@midrange.com
Subject: RE: Embedded SQL exists?



Greg, are you sure?  I tried this and got a Pre-Compiler error saying the
WHERE was not expected.  Also, I don't see WHERE as a valid token in the
SQL reference manual.




                    "Whiteman, Greg"
                    <Greg.Whiteman@transam       To:
"'rpg400-l@midrange.com'"
                    erica.com>                    <rpg400-l@midrange.com>
                    Sent by:                     cc:
                    rpg400-l-admin@midrang       Fax to:
                    e.com                        Subject:     RE: Embedded
SQL exists?


                    11/28/2001 01:29 PM
                    Please respond to
                    rpg400-l






You could incorporate all into one sql. For example:

     C
     C/EXEC SQL
     C+  INSERT INTO EMAILFDST
     C+    VALUES(:EMAILFDST)
     C+ WHERE NOT EXISTS
     C+    (SELECT 1 FROM EMAILFDST
     C+    WHERE EMDFIL=:EMDFIL AND
     C+    EMDCMP=:EMDCMP AND
     C+    EMDFAC=:EMDFAC AND
     C+    EMDUSR=:EMDUSR)


The insert will occur only if no row exists in file EMAILFDST for the
criteria specified in the WHERE clause



_______________________________________________
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/rpg400-l
or email: RPG400-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.