You can provide end users with flexible query options using CL and Query Manager on the iSeries; including the ability to ignore non-selected criteria. This is an issue similar to the one recently addressed in Paul Holm's tip "Finding the Value in SQL and
WebSphere". In this case I have a simple database of two tables and a
view. The two tables are Clients and Services. The view joins Clients
and Services on a Client ID.
GOAL: To allow users to run queries based on most of the fields available.
SOLUTION:
1. Screens are created to prompt for the desired query criteria.
2. A CL program is written to display the screens and format the input
into parameters for a query manager SQL query. (Figure 1)
3. A Query Manager query is written to run the request. (Figure 2)
Note that options are also displayed to allow the user to selected
whether they wish to display or print their report; and whether they
want a full report or just a summary count(In which case a separate
query is run, not shown here.)
Note also that I format some of the selection criteria value ranges in
the CL program and some are handle in the query itself using CASE
statements. The CASE statement allows you to substitute a selected value
or the incoming value from the file if a selection was omitted.
This is a new implementation that will probably be expanded to include
the selection of which fields to include and the sort criteria.
Code
Figure 1 - CL Program to display and process
Query selections.
/* List Clients by Service Dates - Extended query options */
PGM
DCLF CLDATSEL
DCL VAR(&BEGDT) TYPE(*CHAR) LEN(12)
DCL VAR(&ENDDT) TYPE(*CHAR) LEN(12)
DCL VAR(&OUTPUT) TYPE(*CHAR) LEN(6)
DCL VAR(&PRG) TYPE(*CHAR) LEN(22)
DCL VAR(&BEGDB) TYPE(*CHAR) LEN(12)
DCL VAR(&ENDDB) TYPE(*CHAR) LEN(12)
DCL VAR(&SEXI) TYPE(*CHAR) LEN(3)
DCL VAR(&BGZIP) TYPE(*CHAR) LEN(10)
DCL VAR(&EDZIP) TYPE(*CHAR) LEN(10)
DCL VAR(&BEGZP) TYPE(*CHAR) LEN(12)
DCL VAR(&ENDZP) TYPE(*CHAR) LEN(12)
DCL VAR(&BEGMUN) TYPE(*CHAR) LEN(5)
DCL VAR(&ENDMUN) TYPE(*CHAR) LEN(5)
DCL VAR(&CNTY) TYPE(*CHAR) LEN(3)
DCL VAR(&DISAB) TYPE(*CHAR) LEN(3)
DCL VAR(ÐNC) TYPE(*CHAR) LEN(3)
DCL VAR(&BEGGL) TYPE(*CHAR) LEN(7)
DCL VAR(&ENDGL) TYPE(*CHAR) LEN(7)
CHGVAR VAR(&IN31) VALUE('1')
SNDRCVF RCDFMT(CLDATSEL)
IF (&IN03 *OR &IN12) RETURN
/* Set up Service Date selections */
IF (&BEGDAT *EQ ' ') DO
CHGVAR VAR(&BEGDAT) VALUE('1800-01-01')
CHGVAR VAR(&ENDDAT) VALUE('2050-12-31')
ENDDO
ELSE IF (&ENDDAT *EQ ' ') CHGVAR VAR(&ENDDAT)
VALUE(&BEGDAT)
CHGVAR VAR(&BEGDT) VALUE(''''||&BEGDAT||'''')
CHGVAR VAR(&ENDDT) VALUE(''''||&ENDDAT||'''')
CHGVAR VAR(&PRG) VALUE(''''||&PROGSEL||'''')
/* Set up Report Output selections */
IF COND(&DSPPRT *EQ 'D') THEN(CHGVAR VAR(&OUTPUT)
VALUE('*'))
IF COND(&DSPPRT *EQ 'P') THEN(CHGVAR VAR(&OUTPUT)
VALUE('*PRINT'))
/* Display extended selection criteria */
SNDRCVF RCDFMT(SELRANGE)
IF (&IN03 *OR &IN12) RETURN
/* Set up Date of Birth selections */
IF (&BEGDOB *EQ ' ') DO
CHGVAR VAR(&BEGDOB) VALUE('0001-01-01')
CHGVAR VAR(&ENDDOB) VALUE('2099-12-31')
ENDDO
ELSE IF (&ENDDOB *EQ ' ') CHGVAR VAR(&ENDDOB)
VALUE(&BEGDOB)
CHGVAR VAR(&BEGDB) VALUE(''''||&BEGDOB||'''')
CHGVAR VAR(&ENDDB) VALUE(''''||&ENDDOB||'''')
/* Set up Gender selection */
CHGVAR VAR(&SEXI) VALUE(''''||&SEX||'''')
/* Set up Zip Code selections */
IF (&BEGZIP *EQ ' ') DO
CHGVAR VAR(&BGZIP) VALUE(' ')
CHGVAR VAR(&EDZIP) VALUE('9999999999')
ENDDO
ELSE DO
CHGVAR VAR(&BGZIP) VALUE(&BEGZIP||' ')
IF (&ENDZIP *EQ ' ') CHGVAR VAR(&ENDZIP)
VALUE(&BEGZIP)
CHGVAR VAR(&EDZIP) VALUE(&ENDZIP||'99999')
ENDDO
CHGVAR VAR(&BEGZP) VALUE(''''||&BGZIP||'''')
CHGVAR VAR(&ENDZP) VALUE(''''||&EDZIP||'''')
/* Set up Municipality selections */
IF (&BEGMUNC *EQ 0) CHGVAR VAR(&ENDMUNC)
VALUE(99999)
ELSE IF (&ENDMUNC *EQ 0) CHGVAR VAR(&ENDMUNC)
VALUE(&BEGMUNC)
CHGVAR VAR(&BEGMUN) VALUE(&BEGMUNC)
CHGVAR VAR(&ENDMUN) VALUE(&ENDMUNC)
/* Set up County selection */
CHGVAR VAR(&CNTY) VALUE(''''||&COUNTY||'''')
/* Set up Disability selection */
CHGVAR VAR(&DISAB) VALUE(''''||&DISABLED||'''')
/* Set up Ethnicity selection */
CHGVAR VAR(ÐNC) VALUE(''''||ÐNIC||'''')
/* Set up G/L Code selection */
IF (&BEGGLPRG *EQ ' ') CHGVAR VAR(&ENDGLPRG)
VALUE(99999)
ELSE IF (&ENDGLPRG *EQ ' ') CHGVAR VAR(&ENDGLPRG)
VALUE(&BEGGLPRG)
CHGVAR VAR(&BEGGL) VALUE(''''||&BEGGLPRG||'''')
CHGVAR VAR(&ENDGL) VALUE(''''||&ENDGLPRG||'''')
/* Run Query */
IF (&RPTSMC *EQ 'R') STRQMQRY
QMQRY(CCDBQRY/CLNTSERV2) OUTPUT(&OUTPUT) +
QMFORM(CCDBQRY/CLIENTSERV) SETVAR((BEGDAT
&BEGDT) (ENDDAT &ENDDT) +
(PROG &PRG) (BEGDOB &BEGDB) (ENDDOB &ENDDB)
(SEX &SEXI) (BEGZIP +
&BEGZP) (ENDZIP &ENDZP) (BEGMUNC &BEGMUN)
(ENDMUNC &ENDMUN) (CNTY +
&CNTY) (DISAB &DISAB) (ETHNIC ÐNC) (BEGGL
&BEGGL) (ENDGL &ENDGL))
ELSE IF (&RPTSMC *EQ 'C') STRQMQRY
QMQRY(CCDBQRY/CLNTSERC2) +
OUTPUT(&OUTPUT) SETVAR((BEGDAT &BEGDT) (ENDDAT
&ENDDT) (PROG &PRG) +
(BEGDOB &BEGDB) (ENDDOB &ENDDB) (SEX &SEXI)
(BEGZIP &BEGZP) (ENDZIP +
&ENDZP) (BEGMUNC &BEGMUN) (ENDMUNC
&ENDMUN)(CNTY &CNTY) (DISAB +
&DISAB) (ETHNIC ÐNC) (BEGGL &BEGGL) (ENDGL
&ENDGL))
ENDPGM
Figure 2 - Query manager SQL query to run selections.
-- Clients with Services for given Service Date Range
SELECT DISTINCT
-- Columns
A.CSSN, A.LASTNAME, A.FIRSTNAM, A.MI, A.TITLE, A.DOB, A.SEX,
A.TEL,
A.STREET, A.ADDRESS, A.TOWN, A.STATE, A.ZIP, A.MUNCODE, A.COUNTY,
A.EDUCYRS, A.DISABLED, A.MEDCAID#, A.ETHNIC,
SUM(A.SERVALUE)
-- Tables
FROM "CCDB"/"CLIENTSERV" A
WHERE A.SERVDATE BETWEEN &BEGDAT AND &ENDDAT
AND A.PROGDESC = CASE &PROG
WHEN '*ALL' THEN A.PROGDESC
ELSE &PROG
END
AND A.DOB BETWEEN &BEGDOB AND &ENDDOB
AND A.SEX = CASE &SEX
WHEN ' ' THEN A.SEX
ELSE &SEX
END
AND A.ZIP BETWEEN &BEGZIP AND &ENDZIP
AND A.MUNCODE BETWEEN &BEGMUNC AND &ENDMUNC
AND A.COUNTY = CASE &CNTY
WHEN ' ' THEN A.COUNTY
ELSE &CNTY
END
AND A.DISABLED = CASE &DISAB
WHEN ' ' THEN A.DISABLED
ELSE &DISAB
END
AND A.ETHNIC = CASE ÐNIC
WHEN ' ' THEN A.ETHNIC
ELSE ÐNIC
END
AND A.GLPROGNO BETWEEN &BEGGL AND &ENDGL
GROUP BY
AND A.COUNTY = CASE &CNTY
WHEN ' ' THEN A.COUNTY
ELSE &CNTY
END
AND A.DISABLED = CASE &DISAB
WHEN ' ' THEN A.DISABLED
ELSE &DISAB
END
AND A.ETHNIC = CASE ÐNIC
WHEN ' ' THEN A.ETHNIC
ELSE ÐNIC
END
AND A.GLPROGNO BETWEEN &BEGGL AND &ENDGL
GROUP BY
A.CSSN, A.LASTNAME, A.FIRSTNAM, A.MI, A.TITLE, A.DOB, A.SEX,
A.TEL,
A.STREET, A.ADDRESS, A.TOWN, A.STATE, A.ZIP, A.MUNCODE, A.COUNTY,
A.EDUCYRS, A.DISABLED, A.MEDCAID#, A.ETHNIC
==================================
MORE INFORMATION ON THIS TOPIC
==================================
The Best Web Links: tips, tutorials and more.
Ask your programming questions--or help out your peers by answering them--in our live discussion forums.
Ask the Experts yourself: Our application development gurus are waiting to answer your programming questions.
