Re: SQL statement inside a CL

[Scott Klement <midrange-l@xxxxxxxxxxxxxxxx>]

GUY_HENZA@xxxxxxxxxxxxxx wrote:
> It was the $ that needed to be proceeded by a \.  

Glad you figured this out, Guy.

Just in case it's interesting to someone... the $ symbol in Qshell 
represents a variable.  So you could potentially do this:

db2 "select custno,custnm,addr from $lib.custmas where custno=$custno"

It would insert the value of the "lib" environment variable, and the 
value of the "custno" environment variable in the appropriate places in 
the SQL statement.

In CL programming terms that's not as useful as it would be in an actual 
Unix script, since you're building the command on-the-fly from variables 
in your code anyway.  However, if you want to try it from CL, you could 
do something like this:

PGM

   DCL VAR(&CUSTNO) TYPE(*CHAR) LEN(4)  VALUE('1234')
   DCL VAR(&LIB)    TYPE(*CHAR) LEN(10) VALUE('FILELIB')

   ADDENVVAR ENVVAR(custno) VALUE(&CUSTNO) REPLACE(*YES)
   ADDENVVAR ENVVAR(lib)    VALUE(&LIB) REPLACE(*YES)

   STRQSH CMD('db2 "select custno,custnm,addr from $lib.custmas +
                    where custno=$custno"')

ENDPGM

QShell will insert the values set with ADDENVVAR into your SQL 
statement.  Environment variables ARE case-sensitive, so if you try to 
use my code, above, be sure to use "lib" and "custno" instead of "LIB" 
or "CUSTNO".