RE: Re(2): SQL: UPDATE question -- MIDRANGE-L

Why not use RPG?  You can create a logical view from DDS on the fly with
less trouble than this exercise takes.  Why use a screwdriver to pound a
nail?

Joe


> -----Original Message-----
> From: owner-midrange-l@midrange.com
> [mailto:owner-midrange-l@midrange.com]On Behalf Of Pete Hall
> Sent: Friday, June 08, 2001 8:57 PM
> To: MIDRANGE-L@midrange.com
> Subject: Re(2): SQL: UPDATE question
>
>
> 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
+---