Re(2): SQL: UPDATE question

[Pete Hall <pbhall@xxxxxxxxxx>]

At 10:25 06/08/2001, Mike Naughton wrote:
>Thanks very much, but I'm still having trouble :-(
>
>Basically, the problem seems to be that there are more records in Table1
>than in Table2, so there are cases where the SELECT statement returns a
>null value. I'm getting a "null values not allowed" error, and I don't
>really want to allow them (if there is no match in Table2, I don't want to
>do anything to Table1). Might there be a way to use a CASE statement test
>for this? Or maybe start by selecting only the records in Table1 that do
>have a match? I've tried a few things, but I haven't found anything that
>works yet. . . .
You could probably do this:

   UPDATE Table1 SET
     fldOneC = (SELECT fldTwoE FROM Table2
                WHERE KeyTwoA = KeyOneA
                  AND KeyTwoB = KeyOneB),
     fldOneD = (SELECT fldTwoF FROM Table2
                WHERE KeyTwoA = KeyOneA
                  AND KeyTwoB = KeyOneB)
   WHERE EXISTS (SELECT * FROM Table2
                 WHERE KeyTwoA = KeyOneA
                   AND KeyTwoB = KeyOneB)

That seems like a lot of thrashing though. As an alternative, you could 
embed the SQL in an RPG program. Embedded SQL gives you a lot of added 
power. You can use the set manipulation capabilities of SQL where it makes 
sense, and the procedural control of RPG at the same time. Programs like 
this actually perform very well as long as they have the appropriate 
indexes for retrieving data.

    * Declare explicit date formats so SQL and RPG agree
   H DATFMT(*ISO)

    * Declare data types
   D             E DS                 ExtName(Table1) Prefix(Z_)

    * Input record for fetch and update
   D Table1Rcd     DS                 Inz
   D   KeyOneA                        Like(Z_KeyOneA)
   D   KeyOneB                        Like(Z_KeyOneB)
   D   fldOneC                        Like(Z_fldOneC)
   D   fldOneD                        Like(Z_fldOneD)

    * SQL Code Values
   D SQL_OK        C                  0
   D SQL_EOF       C                  100

    * Define the SQL commit behavior and date format
    * Keeps programs from being compiled incorrectly
   C/EXEC SQL SET OPTION COMMIT=*NONE, DATFMT=*ISO
   C/END-EXEC

   C/EXEC SQL DECLARE Table1Csr CURSOR FOR
   C+ SELECT KeyOneA, KeyOneB, fldOneC, fldOneD
   C+ FROM Table1
   C+ FOR UPDATE OF fldOneC, fldOneD
   C/END-EXEC

   C/EXEC SQL DECLARE Table2Csr CURSOR FOR
   C+ SELECT fldTwoE, fldTwoF
   C+ FROM Table2
   C+ WHERE KeyTwoA = :KeyOneA
   C+   AND KeyTwoB = :KeyTwoB
   C+ FOR FETCH ONLY
   C+ OPTIMIZE FOR ONE ROWS
   C/END-EXEC

   C/EXEC SQL OPEN Table1Csr
   C/END-EXEC

    * Check after each SQL statement to make sure
    * it executed as expected
   C              if    SQLCOD <> SQL_OK
   C              exsr  SqlErrorHandler
   C              endif

    * Prime the read/update loop
   C/EXEC SQL FETCH Table1Csr INTO :Table1Rcd
   C/END-EXEC

   C              dow   SQLCOD = SQL_OK
    * Parameter value changes require a new open
    * but the cursor will actually be reused by SQL
    * so it has low impact on performance
   C/EXEC SQL OPEN Table2Csr
   C/END-EXEC

   C              if    SQLCOD <> SQL_OK
   C              exsr  SqlErrorHandler
   C              endif

    * Get the update values
   C/EXEC SQL FETCH Table2Csr INTO :fldOneC, :fldOneD
   C/END-EXEC

    * If a row was found, update Table1
   C              if    SQLCOD = SQL_OK
   C/EXEC SQL UPDATE Table1
   C+ SET fldOneC = :fldOneC,
   C+     fldOneD = :fldOneD
   C+ WHERE CURRENT OF Table1Csr
   C/END-EXEC
   C              endif

    * This handles no record found in Table2
   C              if    SQLCOD = SQL_EOF
   C              eval  SQLCOD = SQL_OK
   C              endif

   C              if    SQLCOD <> SQL_OK
   C              exsr  SqlErrorHandler
   C              endif

    * Close the input cursor so it can be
    * reopened again at the top of the loop
   C/EXEC SQL CLOSE Table2Csr
   C/END-EXEC

   C              if    SQLCOD <> SQL_OK
   C              exsr  SqlErrorHandler
   C              endif

    * Get the next row for update
   C/EXEC SQL FETCH Table1Csr INTO :Table1Rcd
   C/END-EXEC

   C              enddo

    * Should have processed all rows from table1 here,
    * and have received an end of file on the last fetch
   C              if    SQLCOD <> SQL_EOF
   C              exsr  SqlErrorHandler
   C              endif

   C/EXEC SQL CLOSE Table1Csr
   C/END-EXEC
    * You could check SQLCOD here too if you're a real zealot.

   C              return


Pete Hall
pbhall@execpc.com
http://www.execpc.com/~pbhall/

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---