Set up flexible user queries using CL and query manager

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.

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(&ETHNC) 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(&ETHNC) VALUE(''''||&ETHNIC||'''')


/* 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 &ETHNC) (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 &ETHNC) (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 &ETHNIC                            WHEN ' ' THEN A.ETHNIC
                            ELSE &ETHNIC                       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 &ETHNIC
                            WHEN ' ' THEN A.ETHNIC

                            ELSE &ETHNIC
                       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.