midrange.com code scratchpad
Name:
Mark S Waterbury
Scriptlanguage:
Plain Text
Tabwidth:
4
Date:
03/27/2019 03:10:28 pm
IP:
Logged
Description:
how to do embedded SQL in CL
Code:
  1.  
  2.              PGM
  3.              DCL        VAR(&MSG) TYPE(*CHAR) LEN(100)
  4.              DCL        VAR(&ZIP) TYPE(*CHAR) LEN( 6)
  5.              DCL        VAR(&BAL) TYPE(*CHAR) LEN(10)
  6.              DCL        VAR(&SPC) TYPE(*CHAR) LEN( 5)
  7.              DCLF       FILE(QTEMP/QCUSTCDT1)
  8.          /* create SQL VIEW in QTEMP ... */
  9.              RUNSQL     SQL('CREATE OR REPLACE VIEW QTEMP/QCUSTCDT1 +
  10.                           AS SELECT CUSNUM, LSTNAM, INIT, STREET, +
  11.                           CITY, STATE, ZIPCOD, CDTLMT, CHGCOD, +
  12.                           BALDUE, CDTDUE FROM QIWS/QCUSTCDT') +
  13.                           COMMIT(*NONE)
  14.              OVRDBF     FILE(QCUSTCDT1) TOFILE(QTEMP/QCUSTCDT1) +
  15.                           MBR(*FIRST) SECURE(*YES) OVRSCOPE(*JOB) +
  16.                           SHARE(*YES) SEQONLY(*YES)
  17.           /* create Open Data Path (ODP) for result set ... */
  18.              OPNQRYF    FILE((QTEMP/QCUSTCDT1 *FIRST *ONLY)) +
  19.                           OPTION(*INP) KEYFLD((STATE *ASCEND) (CITY +
  20.                           *ASCEND) (LSTNAM *ASCEND))
  21.  READ:
  22.              RCVF
  23.              MONMSG     MSGID(CPF0864) EXEC(GOTO EOF)
  24.           /* echo the data to the session and job log ... */
  25.              CHGVAR     VAR(&ZIP) VALUE(&ZIPCOD)
  26.              CHGVAR     VAR(&BAL) VALUE(&BALDUE)
  27.              CHGVAR     VAR(&MSG) VALUE(&LSTNAM *TCAT &INIT *TCAT &SPC +
  28.                           *TCAT &STREET *TCAT &SPC *TCAT &CITY *TCAT &SPC +
  29.                           *TCAT &STATE *TCAT &SPC *TCAT &ZIP *TCAT &SPC *TCAT &BAL)
  30.              SNDPGMMSG  MSGID(CPF9898) MSGF(QSYS/QCPFMSG) +
  31.                           MSGDTA(&MSG) TOPGMQ(*EXT) MSGTYPE(*STATUS)
  32.              SNDPGMMSG  MSGID(CPF9898) MSGF(QSYS/QCPFMSG) +
  33.                           MSGDTA(&MSG) TOPGMQ(*PRV (*)) MSGTYPE(*COMP)
  34.           /* repeat until EOF ... */
  35.              GOTO       READ
  36.  EOF:
  37.              CLOF       OPNID(QCUSTCDT1)
  38.              DLTOVR     FILE(QCUSTCDT1) LVL(*JOB)
  39.           /* exit */
  40.              RETURN
  41.              ENDPGM
  42.  
  43.  
© 2004-2019 by midrange.com generated in 0.007s valid xhtml & css