RE: update query with case statement

[qsrvbas@xxxxxxxxxxxx (Tom Liotta)]

Joel:

At first glance, it looks like you have a substitution variable named "&file" 
but you're passing a value in for "&FILE". Note the difference in case. QM 
query substitution variable names are case-sensitive.

If so, either change your update statement or put quotes around the name of the 
variable in the STRQMQRY SETVAR() parameter to force lower-case.

Tom Liotta

midrange-l-request@midrange.com wrote:

>   9. update query with case statement (Joel Fritz)
>------------------------------
>
>I'm slowly learning SQL on a need to know basis.  I needed to do a
>conditional update so I tried the case statement.  My query looks like this:
>
>update &file
>set field1 = case when (field2 = '0')
>                     then 'Final Totals'
>                when (field2 = '1')
>                     then 'Store Totals'
>                else field1
>                end
>
>Works great interactively with the substitution variable, but dies in batch
>with a message that says: " Message . . . . :   Global variable prompting is
>not allowed in batch mode.  Cause . . . . . :   You tried to use the
>variable
>   "file                          " before it was set.  Prompting for
>variables
>   cannot be done in batch mode. "  It goes on to advise using the setvar
>parm, which I did in the CL.  The strqmqry statement was:
>
>  DCL        VAR(&UPDFILE) TYPE(*CHAR) LEN(20) +
>               VALUE('LIBRARY/FILENAME')
>  STRQMQRY   QMQRY(QRYNAME) SETVAR((FILE +
>               &UPDFILE))

-- 
-- 
Tom Liotta
The PowerTech Group, Inc.
19426 68th Avenue South
Kent, WA 98032
Phone  253-872-7788 x313
Fax    253-872-7904
http://www.powertechgroup.com


__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now! 
http://channels.netscape.com/ns/browsers/download.jsp 

Get your own FREE, personal Netscape Mail account today at 
http://webmail.netscape.com/