help with SQL in CL

I built a RUNSQL command from a magazine article.

Intermittently it will fail because one space is dropped between parm &v8 and &v9.

Any suggestions how I can solve this?

Is there a better way to do this so there is one parm len 1100 instead of 20 parms length 55?

Thanks!




===> RUNSQL STMT('select AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DATE,AAA
AA_SUPRSHIP_ID_NBR, AAAAA_LOCA
TION_FORMAT_CODe , AAAAA_ORIG
IN_CLIENT_NBR , AAAAA_SHIP
_BILLED_DEST_COde,AAAAA_COMMODITY_CODE,AAAAA_SHIPMENT_TYPE_CODE,AAAAA_SCALE_TICK
ET_NBR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_TYPE_CODE from SHIPFILE')
/|\
|
|
This space disappears and -------------
Causes SQL statement to fail!!



Type SQL Statement
************************** Beginning of Data *********************************
0001.00 &V1&V2&V3&V4&V5&V6&V7&V8&V9&V10&V11&V12&V13&V14&V15&V16&V17&V18&V19&V20
***************************** End of Data ************************************



RUNSQL STMT('select AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DATE,A0
176_SUPRSHIP_ID_NBR, A0
176_LOCATION_FORMAT_CODe ,
AAAAA_ORIGIN_CLIENT_NBR ,
AAAAA_SHIP_BILLED_DEST_COde,AAAAA_COMMODITY_CODE,AAAAA_SHIPME
NT_TYPE_CODE,AAAAA_SCALE_TICKET_NBR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_
TYPE_CODE from shipment')
2600 - STRQMQRY QMQRY(RUNSQL2) SETVAR((V1 'select
AAAAA_SHIPMENT_U_ID_NBR, AAAAA_BILL_OF_LADING_DA') (V2
'TE,AAAAA_SUPRSHIP_ID_NBR,') (V3 '
AAAAA_LOCATION_FORMAT_CODe ,') (V4 '
AAAAA_ORI') (V5 'GIN_CLIENT_NBR ,') (V6 '
AAAAA_SHIP_BILLED_DEST_COde,AAAAA_COMMO') (V7
'DITY_CODE,AAAAA_SHIPMENT_TYPE_CODE,AAAAA_SCALE_TICKET_N') (V8
'BR,AAAAA_APP_ATTMPTD_NBR,AAAAA_SHIPMENT_TYPE_CODE from') (V9
'shipment') (V10 ' ') (V11 ' ') (V1 ^------------------------------space is now gone, so SQL fails!!!
Token <END-OF-STATEMENT> was not valid. Valid tokens: , FROM INTO.
RUN QUERY command failed with SQLCODE -104.
RUN QUERY command ended due to error.
STRQMQRY command failed.
QWM2701 received by RUNSQL at 2600. (C D I R)
? C
Function check. QWM2701 unmonitored by RUNSQL at statement 2600,
instruction X'00D5'.

RUNSQL CL:

CMD PROMPT('Run SQL Statement')
PARM KWD(STMT) TYPE(*CHAR) LEN(1100)

RUNSQL CL:

PGM PARM(&STMT)
DCL VAR(&STMT) TYPE(*CHAR) LEN(1100
DCL VAR(&V1) TYPE(*CHAR) LEN(55)
DCL VAR(&V2) TYPE(*CHAR) LEN(55)
DCL VAR(&V3) TYPE(*CHAR) LEN(55)
DCL VAR(&V4) TYPE(*CHAR) LEN(55)
DCL VAR(&V5) TYPE(*CHAR) LEN(55)
DCL VAR(&V6) TYPE(*CHAR) LEN(55)
DCL VAR(&V7) TYPE(*CHAR) LEN(55)
DCL VAR(&V8) TYPE(*CHAR) LEN(55)
DCL VAR(&V9) TYPE(*CHAR) LEN(55)
DCL VAR(&V10) TYPE(*CHAR) LEN(55)
DCL VAR(&V11) TYPE(*CHAR) LEN(55)
DCL VAR(&V12) TYPE(*CHAR) LEN(55)
DCL VAR(&V13) TYPE(*CHAR) LEN(55)
DCL VAR(&V14) TYPE(*CHAR) LEN(55)
DCL VAR(&V15) TYPE(*CHAR) LEN(55)
DCL VAR(&V16) TYPE(*CHAR) LEN(55)
DCL VAR(&V17) TYPE(*CHAR) LEN(55)
DCL VAR(&V18) TYPE(*CHAR) LEN(55)
DCL VAR(&V19) TYPE(*CHAR) LEN(55)
DCL VAR(&V20) TYPE(*CHAR) LEN(55)

CHGVAR &V1 %SST(&STMT 001 55)
CHGVAR &V2 %SST(&STMT 056 55)
CHGVAR &V3 %SST(&STMT 111 55)
CHGVAR &V4 %SST(&STMT 166 55)
CHGVAR &V5 %SST(&STMT 221 55)
CHGVAR &V6 %SST(&STMT 276 55)
CHGVAR &V7 %SST(&STMT 331 55)
CHGVAR &V8 %SST(&STMT 386 55)
CHGVAR &V9 %SST(&STMT 441 55)
CHGVAR &V10 %SST(&STMT 496 55)
CHGVAR &V11 %SST(&STMT 551 55)
CHGVAR &V12 %SST(&STMT 606 55)
CHGVAR &V13 %SST(&STMT 661 55)
CHGVAR &V14 %SST(&STMT 716 55)
CHGVAR &V15 %SST(&STMT 771 55)
CHGVAR &V16 %SST(&STMT 826 55)
CHGVAR &V17 %SST(&STMT 881 55)
CHGVAR &V18 %SST(&STMT 936 55)
CHGVAR &V19 %SST(&STMT 991 55)
CHGVAR &V20 %SST(&STMT 1046 55)


STRQMQRY QMQRY(RUNSQL2) SETVAR((V1 &V1) (V2 &V2) (V3 +
&V3) (V4 &V4) (V5 &V5) (V6 &V6) (V7 &V7) +
(V8 &V8) (V9 &V9) (V10 &V10) (V11 &V11) +
(V12 &V12) (V13 &V13) (V14 &V14) (V15 +
&V15) (V16 &V16) (V17 &V17) (V18 &V18) +
(V19 &V19) (V20 &V20))

ENDPGM








______________________________________________________________________
This outbound email has been scanned for all viruses by the MessageLabs Skyscan service.
For more information please visit http://www.symanteccloud.com
______________________________________________________________________