|
midrange.com
This is the simplest I can find right now. Although the select is
involved, I am just getting a worker number. There is extensive use of
copybooks, but I imagine you are mostly interested in the flow.
hth,
Dave Boettcher
HALWNULL(*inputonly)
HDATFMT (*ISO)
HDFTACTGRP(*NO)
Hactgrp(*caller)
/IF DEFINED(NEED_SQLCA)
/COPY QGPL/COPYBOOK,sqlca_ds
/ENDIF
/copy QGPL/COPYBOOK,D_STDTYP
/copy QGPL/COPYBOOK,fiparms
/copy QGPL/COPYBOOK,TimeDate
D* ASSIGNFLDS E DS EXTNAME(ASSIGNMENT)
D* PReFIX(A)
/copy QGPL/COPYBOOK,D_assignmn
/copy QGPL/COPYBOOK,D_PERSON
D* PERSONFLDS E DS EXTNAME(PERSON)
D* PReFIX(PR)
D* WorkerFLDS E DS EXTNAME(WORKER)
D* PReFIX(W)
/copy QGPL/COPYBOOK,D_WORKER
d childname s 36A
d childname2 s like(childname)
d childnameu s like(childname)
d childnameu2 s like(childname)
d DefltWrkr C '1 '
D SDS
D PgmNam *PROC
D USERID 254 263
C/EJECT
c/exec sql
c+ set option commit = *none, CloSQLCsr = *EndMod
c/end-exec
C If *INLR <> *INLR
c *entry Plist
C Parm Parms
C/EXEC SQL
C+
C+ DECLARE CsrWrkrjoin cursor for
C+ SELECT
C+ assignment.tx_asgn_dscr,
C+ worker.id_em00001,
C+ assignment.CD_ASGN_ROLE,
C+ person.nm_lst,
C+ person.nm_frst,
C+ assignment.CD_ASGN_TYPE,
C+ assignment.CD_ASGN_GRP,
C+ assignment.CD_ASGN_CTGRY,
C+ assignment.CD_ASGN_RSPNS,
C+ assignment.DT_STRT,
C+ assignment.ID_PRSN,
C+ assignment.DT_END
C+ FROM worker
C+ JOIN assignment
C+ ON worker.id_prsn = assignment.id_prsn
C+ JOIN person
C+ ON worker.id_prsn = person.id_prsn
C+ WHERE
C+ assignment.cd_asgn_ctgry = 1
C+ and assignment.fl_open = 'Y'
C+ and assignment.CD_ASgn_type <> 14
c+ and (assignment.tx_asgn_dscr =
:childname
c+ OR assignment.tx_asgn_dscr =
:childname2
c+ OR assignment.tx_asgn_dscr =
:childnameU
c+ OR assignment.tx_asgn_dscr =
:childnameU2)
C+ ORDER By
C+ assignment.tx_asgn_dscr,
C+ assignment.cd_asgn_role,
C+ assignment.dt_strt desc
C+ For READ ONLY
C+
C/END-EXEC
C Endif
C*
* New program flow
C Eval ChildName = %trim(ChildLast) +
onespace
C + comma + onespace
C + %trim(ChildFirst) + onespace
C + ChildMI + Dot
C Eval ChildName2 = %trim(ChildLast)
C + comma + onespace
C + %trim(ChildFirst) + onespace
C + ChildMI + Dot
C Eval ChildNameU =
C %xlate(lowr:uppr:%trim(ChildLast)) +
onespace
C + comma + onespace
C +
%xlate(lowr:uppr:%trim(ChildFirst))
C + onespace + ChildMI + Dot
C Eval ChildName2 =
C %xlate(lowr:uppr:%trim(ChildLast))
C + comma + onespace
C +
%xlate(lowr:uppr:%trim(ChildFirst))
C + ChildMI + Dot
C/EXEC SQL
C+ OPEN CsrWrkrjoin
C/END-EXEC
*
C/EXEC SQL
C+ FETCH NEXT From CsrWrkrjoin
C+ INTO
C+ :atx_asgn_dscr :nl_tx_asgn_dscr,
C+ :WID_EMPL_NMBR :NL_ID_EMPL_NMBR,
C+ :aCD_ASgn_role :nl_CD_ASgn_role,
C+ :nm_lst :nl_nm_lst,
C+ :nm_frst :nl_nm_frst,
C+ :aCD_ASgn_type :nl_CD_ASgn_type,
C+ :aCD_ASgn_grp :nl_CD_ASgn_grp,
C+ :aCD_ASgn_ctgry :NL_cd_asgnctgry,
C+ :aCD_ASgn_rspns :NL_cd_asgnrspns,
C+ :aDT_STRT :nl_DT_STRT,
C+ :aID_PRSN :nl_ID_PRSN,
C+ :aDT_END :nl_DT_END
C/END-EXEC
*
/copy QGPL/COPYBOOK,SQLSTTRTN
C Exsr Endrtn
C SQLNoRow Begsr
C Eval Worker = '1 '
C Endsr
C ProcessRow Begsr
C if nl_id_empl_nmbr >= 0
C Eval Worker = %trim(WID_EMPL_NMBR)
C Else
C Eval Worker = DefltWrkr
C Endif
c endsr
C SQLWarning BEGSR
C Endsr
C SQLError Begsr
C Eval Worker = '1 '
C Endsr
C EndRtn BEGSR
C/EXEC SQL
C+ CLOSE CsrWrkrjoin
C/END-EXEC
C Eval *INLR = *ON
C Return
C Endsr
C/SPACE 2
C *inzsr Begsr
C Time Tyme
/copy QGPL/COPYBOOK,C_assignmn
/copy QGPL/COPYBOOK,C_PERSON
/copy QGPL/COPYBOOK,C_WORKER
C Endsr
c*
>>> RPower@xxxxxxxxxx 05/12/2005 7:07:38 AM >>>
Guys,
fellow developer here is learning SQL.
Whilst the book he is using is technically and shows us all the nice
little things, it fails to show the entire picture. Would someone out
there be willing to share a program using SQL so that we might look at
a
real example? TIA,
Ron Power
Programmer
Information Services
City Of St. John's, NL
P.O. Box 908
St. John's, NL
A1C 5M2
Tel: 709-576-8132
Email: rpower@xxxxxxxxxx
Website: http://www.stjohns.ca/
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2025 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
