Re: Field value (sample - long)

[Buck Calabro <buck.calabro@xxxxxxxxxxxxxxxxx>]

Vini Kolathur wrote:

>I have a file which holds a list of customers 
>in one field  and fields "names" in another 
>field. These fields mean that they are
>mandatory fields for that particular 
>customer in the Master database. This idea 
>came just to make the validation routine 
>more generic since diff customers have 
>diff conditions for a field being mandatory or
>not. So if the validation routine finds any of 
>these "fields" not having a value in the master 
>database then it triggers of a email and
>so on.

Vini,
This is an interesting challenge.  You have a bunch of unpleasant problems
facing you.  I spent a few moments with SQL and came up with this
"boilerplate" code.  It has no error handling and assumes that the values
you are looking for are character, not decimal.  If you try it with a
decimal, you'll get a -303 SQLCOD, telling you that the host variable is not
compatible.  It's also missing the WHERE clause on the prepared SELECT
statement - that's where you'd select "that particular customer."  All in
all it was an interesting exercise.

As for Hans' comment about being not recommended, the power of indirect
addressing is easy to abuse, and I would seriously consider re-thinking your
approach.  It may be easier to write a small procedure for each field and
wrap the lot in a "driver" procedure that will fire the proper
sub-procedure.

Here is the source code:
     A          R RFLDNAME
     A*
     A            FLDNAME       10A
     A*
     A          K FLDNAME

     H Debug

     c* Example of indirect reference to fields
     c* CRTSQLRPGI *CURLIB/FLDNAMES Dbgview(*Source) Objtype(*Module)
     c* CRTPGM PGM(FLDNAMES) ACTGRP(QILE) DETAIL(*BASIC)

     d TRUE            s             10i 0            inz(0)
     d FALSE           s                   like(TRUE) inz(-1)
     d DBField         s             10a
     d DBFieldState    s                   like(TRUE)
     d FldEOF          s                   like(TRUE)
     d FldMsg          s             50

     d                Sds
     d JobName               244    253
* Job name

     d CheckMand       pr                  like(DBFieldState)
     d  FldName                            like(DBField) const


     c* Spin through the database file looking for field names
     c/exec sql
     c+ declare Field cursor for
     c+   Select FLDNAME from FLDNAMES
     c+     order by FLDNAME
     c/end-exec

     c/exec sql
     c+ Open Field
     c/end-exec

     C                   Eval      FldEOF = FALSE
     C                   DoW       FldEOF = FALSE
     c/exec sql
     c+ Fetch next from Field into :DBField
     c/end-exec
     C                   If        SQLCOD <> 0
     C                   Eval      FldEOF = TRUE
     C                   Else
     C                   Eval      DBFieldState = CheckMand(DBField)
     C                   Eval      FldMsg = 'Field ' + %trim(DBField) +
     C                                      ' = ' + %editc(DBFieldState:'X')
     C     FldMsg        Dsply     JobName
     C                   EndIF

     C                   EndDo

     c/exec sql
     c+ Close Field
     c/end-exec

     C                   Eval      *InLR = *On

      * Check to see that the passed in field name contains
      * data.  Since this is a boilerplate, error handling is minimal.
     p CheckMand       b
     d CheckMand       pi                  like(DBFieldState)
     d  FldName                            like(DBField) const

     d FieldState      s                   like(TRUE)
     d SqlStm          s            512a
     d FldData         s            512a

     c                   Eval      FieldState = FALSE

      * We'll prepare a dynamic SQL statement to see if the field
      * contains data or not

     c/exec sql
     c+ Declare FldTest cursor for DynFldTest
     c/end-exec

     c                   eval      SqlStm = 'Select ' + %trim(FldName) +
     c                                      ' from Master'

     c/exec sql
     c+ Prepare DynFldTest from :SqlStm
     c/end-exec

     c/exec sql
     c+ Open FldTest using :SqlStm
     c/end-exec

     c/exec sql
     c+ Fetch next from FldTest into :FldData
     c/end-exec

     C                   If        SQLCOD = 0
     C                   if        FldData <> *Blanks
     C                   Eval      FieldState = TRUE
     C                   endIf
     C                   EndIF

     c/exec sql
     c+ Close FldTest
     c/end-exec

     c                   Return    FieldState
     p                 e

Buck Calabro
Aptis; Albany, NY

"Advice worth every penny you paid for it..."
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---