AS/400 Code Samples - AS/400 - COUNTSEQ - GSS Count unique occurances of a value in a file field Author: George Pearson Company: Green Springs Software, Inc. www.green-springs.com Purpose: Programmers Utility to list discrete entries from a database file. Uses ASC's Sequel Product Components: COUNT CMD COUNTC CL Program Parameters: File Enter Qualified or Unqualified File Name Special Values: *Libl Field Enter a valid field name in the file to count ocuurances of Output ("*" or "*Print") Written: 1998 /*ã*************************************************************************/ /* */ /* ¹ Green Springs Software, Inc. Ashland, OR */ /* */ /*ã*************************************************************************/ /* */ /* COMMAND ID - COUNT */ /* CPP - COUNTC OR COUNTSQLC * */ /* DESCRIPTION - COUNT VALUES IN A FIELD */ /* WRITTEN BY - GEORGE PEARSON, GREEN SPRINGS SOFTWARE, INC. */ /* DATE CREATED - 9/28/1998 */ /* PURPOSE - EXECUTES A SEQUEL TO COUNT THE VALUES IN A FIELD. */ /* */ /* NOTES - * COUNTC requires ASC's Sequel Product. */ /* COUNTSQLC uses IBM's SQL/400 */ /* */ /*ã*************************************************************************/ /* */ /* (C) MMIII GREEN SPRINGS SOFTWARE, INC. */ /* POB 3336, ASHLAND, OR 97520 */ /* (541) 488-2560 george@green-springs.com */ /* www.green-springs.com */ /* */ /*ã*************************************************************************/ CMD PROMPT('GSS COUNT VALUES IN A FIELD') PARM KWD(FILE) TYPE(QUAL1) MIN(1) MAX(1) + PROMPT('FILE TO ANALYZE?:' 1) PARM KWD(FIELD) TYPE(*CHAR) LEN(10) MIN(1) MAX(1) + PROMPT('FIELD TO COUNT?' 2) PARM KWD(OUTPUT) TYPE(*CHAR) LEN(6) RSTD(*YES) + DFT(*) VALUES(* *PRINT) MAX(1) + PROMPT('OUTPUT: * OR *PRINT' 4) QUAL1: QUAL TYPE(*NAME) LEN(10) MIN(1) EXPR(*YES) QUAL TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*LIBL)) PROMPT('IN LIBRARY:') /*ã************** End Of Source *******************************************/ /*ã*************************************************************************/ /* */ /* ¹ Green Springs Software, Inc. Ashland, OR */ /* */ /*ã*************************************************************************/ /* */ /* PROGRAM ID - COUNTC ASC's SEQUEL Version */ /* DISPLAY FILE - NONE */ /* DESCRIPTION - Count & List Occurances in a field */ /* WRITTEN BY - George Pearson, Green Springs Software, Inc. */ /* DATE CREATED - 9/28/2003 */ /* PURPOSE - Executes an SQL to Count & List the values in a fld. */ /* */ /* NOTES - Requires ASC's SEQUEL Product for this to work. */ /* See CL Program COUNTSQLC for IBM SQL Version. */ /* */ /*ã*************************************************************************/ /* */ /* (C) MMIII Green Springs Software, Inc. */ /* POB 3336, Ashland, OR 97520 */ /* (541) 488-2560 george@green-springs.com */ /* www.green-springs.com */ /* */ /*ã*************************************************************************/ PGM PARM(&FILLIB &FLD &OUTPUT) DCL &FILLIB *CHAR LEN(21) DCL &FILE *CHAR LEN(10) DCL &FLD *CHAR LEN(10) DCL &LIB *CHAR LEN(10) DCL &MBR *CHAR LEN(10) DCL &OUTPUT *CHAR LEN(06) DCL &SQL *CHAR LEN(200) DCL &TYPE *CHAR LEN(01) MONMSG MSGID(CPF9800 CPF2981) EXEC(GOTO + CMDLBL(ERR01)) RTVJOBA TYPE(&TYPE) CHGVAR VAR(&FILE) VALUE(%SST(&FILLIB 1 10)) CHGVAR VAR(&LIB) VALUE(%SST(&FILLIB 11 10)) CHKOBJ OBJ(&LIB/&FILE) OBJTYPE(*FILE) CHGVAR VAR(&SQL) VALUE('SELECT ' *CAT &FLD *TCAT ', + COUNT(*) NAME(COUNT) FROM ' *CAT &LIB + *TCAT '/' *CAT &FILE *BCAT ' GROUP BY ' + *CAT &FLD *CAT ' ORDER BY ' *CAT &FLD) IF COND(&TYPE = '0') THEN(CHGVAR &OUTPUT '*PRINT') IF COND(&OUTPUT = '*PRINT') THEN(SEQUEL/PRINT + SQL(&SQL)) IF COND(&OUTPUT = '* ') THEN(SEQUEL/DISPLAY + SQL(&SQL)) RETURN ERR01: SNDPGMMSG MSG('File ' *CAT &LIB *TCAT '/' *CAT &FILE + *TCAT ', Mbr:' *CAT &MBR *TCAT ' not found.') ENDPGM: ENDPGM /* ******** End Of Source (COUNTC) CL Program *************************/