RE: easier SQL statements in CL

Good point.

I simplified an SQL sample too much. Normally there are a bunch of variables in there, so it would look more like the following:

Is it possible to copy this from a CL source member (in-line with other CL commands) into a qtemp member use RUNSQLSTM to run that?

CHGVAR &SQLCMD ('INSERT INTO LGFILE +
(LG_CLIENT, LG_SEQ, LG_DATE, LG_TIME, LG_DIR, LG_ID, +
LG_MODE, LG_NRECS, +
LG_ERRCDE, LG_TYPE, LG_FORMAT, LG_SOURCE, LG_TARGET, +
LG_JOBID +
) VALUES (' *tcat +
&QT *cat &P_CLIENT *cat &QT_C *cat +
&QT *cat &P_SEQ *cat &QT_C *cat +
&QT *cat &CUR_YYYYMD *cat &QT_C *cat +
&QT *cat &CUR_HHMMSS *cat &QT_C *cat +
&QT *cat &P_DIR *cat &QT_C *cat +
&QT *cat &P_ID *cat &QT_C *cat +
&QT *cat &MODE *cat &QT_C *cat +
&QT *cat &P_NRECS *cat &QT_C *cat +
&QT *cat &P_ERRCDE *cat &QT_C *cat +
&QT *cat &P_TYPE *cat &QT_C *cat +
&QT *cat &P_FORMAT *cat &QT_C *cat +
&QT *cat &P_SOURCE *cat &QT_C *cat +
&QT *cat &P_TARGET *cat &QT_C *cat +

Etc.


-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of CRPence
Sent: Friday, May 11, 2012 2:38 PM
To: midrange-l@xxxxxxxxxxxx
Subject: Re: easier SQL statements in CL

On 11 May 2012 12:01, Stone, Joel wrote:

There MUST be a better method of coding SQL in a CL source member.

Here is what an SQL stmt might look like that I created today:

PGM
DCL &SQLCMD type(*CHAR) len(1000)
chgvar &sqlCmd ('create table qtemp/sqltemp1' +
*tcat ' (fld1 char(1),' +
*tcat ' fld2 char(1),' +
*tcat ' fld3 char(1),' +
*tcat ' clientN numeric(6,0))')

RUNSQL(&sqlCmd)

<<SNIP>>

What is with all the *TCAT and extra apostrophes? Just use standard
line continuation to allow the literal\constant string to span lines:

chgvar &sqlCmd ( +
'create table qtemp/sqltemp1 +
(fld1 char(1) +
,fld2 char(1) +
,fld3 char(1) +
,clientN numeric(6,0))' )

Or move the line continuation characters way to the right, optionally
adding blank lines:

chgvar &sqlCmd (' +
+
create table qtemp/sqltemp1 +
(fld1 char(1) +
,fld2 char(1) +
,fld3 char(1) +
,clientN numeric(6,0)) +
+
' )

Regards, Chuck