Re: update query with case statement

[pnelson@xxxxxxxxxx]

   Joel,

   You can do this with the scripting feature of SEQUEL from Ad= vanced
   Systems Concepts www.asc-iseries.com

   There is a command called= BCHSCRIPT, with which you can pass variables
   into the script just before i= t goes to batch.

   Joel Fritz <JFritz@xxxx= perimage.com>
   Sent by: midrange-l-bounces@x= idrange.com
   02/13/2003 03:03 PM PST
   <FON= T SIZE=2>Please respond to Midrange Systems Technical
   Discussion<B= R>
   To: "MIDRANGE-L@xxxxxxxxx = com (E-mail)" <MIDRANGE-L@xxxxxxxxxxxx>
   cc:
   bcc:
   Subject:</= FONT> update query with case statement

   </= P>

   I'm slowly learning SQL on a need to = know basis.  I needed to do a
   conditional update so I tried the cas= e statement.  My query looks like
   this:
   update &file
   set field1 = case when (field2 = '= 0')
                     &nbs= p; then 'Final Totals'
                =  when (field2 = '1')
               =         then 'Store Totals'
         &n= bsp;        else field1
          =        end
   Works great interactively with the substitution variable, but dies in b   
atch
   with a message that says: " Message . . . . :   Global va= riable
   prompting is
   not allowed in batch mode.  Cause . . . . . : &= nbsp; You tried to use
   the
   variable
     "file     =                     &nbsp= ;" before it was set.  Prompting
   for
   variables
     cann= ot be done in batch mode. "  It goes on to advise using the setva   r
   parm, which I did in the CL.  The strqmqry statement was:
   </FO= NT>
    DCL       &nb= sp;VAR(&UPDFILE) TYPE(*CHAR) LEN(20) +
           =       VALUE('LIBRARY/FILENAME')
    STRQMQRY   QM= QRY(QRYNAME) SETVAR((FILE +
               &= nbsp; &UPDFILE))

   If = I run a query with substitution variables defined as above in batch
   some= thing like :
   update &fil= e set &set where &where
   it works fine.
   My workar= ound was to hard code the file name in the query.  Am I doing
   somet= hing wrong?
   ____________________   _________________________   __
   This is the Midrange Systems Technical Discussion (MIDRANGE-L) ma= iling
   list
   To post a message email: MIDRANGE-L@xxxxxxxxxxxx
   To subscr= ibe, unsubscribe, or change list options,
   visit: http://lists.midrange.com/mailman/= listinfo/midrange-l
   or email: MIDRANGE-L-request@xxxxxxxxxxxx
   Bef= ore posting, please take a moment to review the archives
   at http://archive.midrange.com/midrange-l= .