Set up flexible user queries using CL and query manager
Provide end users with flexible query options using CL and Query Manager on the iSeries
You can provide end users with flexible query options using CL and Query Manager on the iSeries; including the...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
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(Ð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.