Extracting PO Boxes from database 4

[progolf069]

This problem has had me stumpted for the last couple of days.

My boss wants me to take a customer database that we have, and have a report printout for any PO Boxes that would be in the address field of the database. We are wanting to be able to use this database to send UPS shipments to our customers, but of course UPS does not ship to PO Boxes!

So does COBOL, like SQL, support any commands/statements like a LIKE operator? Right now I am about to code my program where it will test every character in the address field, and if I am able to extract (or pull) all of the letters "POBX" from all of the characters in the address field, I would then extract that record for printing.

But I would think this would have any easier way of being done, besides testing all of the characters in the given field. Anybody have any ideas, or samples of a like situation that they would like to share? Thanks for everybodies help! Have a great one!

 

[pipk]

Can't you just query the database with embedded sql?

 

[progolf069]

Embedded SQL is not going to be an option, since our version of MicroFocus COBOL is an old version 3.2.20! Copyrighted 1992-1994! Which in turn, we do not have a SQL server set up. My only mention to SQL was to easily explain what I am actually trying to do in simple terms. I need a way for this to work with COBOL commands, if possible. Thanks for your assistance Pip! If anybody has any ideas, please send them on over! Thanks again for all of your help!

 

[pipk]

can you do a couple "inspect tallying" statements looking for say "X" and "P" and then doing processing if your tally fields are > 0.

it is a while since I touched Cobol and seeing as RonaldB had me eating humble pie earlier I have lost a bit of confidence....

let me know.

as far as I am aware there is no simple solution akin to an SQL 'like' query.

 

[slade]

Hi Pro,

There are a number of ways to do this. You mentioned one, Pipk another. My entry is:

move spaces to delim-fld
unstring addr-rec into dmmy-fld delimited by 'pobx' delimiter in delim-fld
if delim-fld = 'pobx'
save the rec
else
proc nxt rec
end-if

They all devolve to the same thing: loop thru the fld for 'pobx'.

Regards, Jack.

 

[Thane]

When I need to do this I use the SQL commands to extract only the records I need to a CSV file then use them.

When the source is not an RDB, I use Inspect after converting to all upper case and squishing out spaces if necessary.

 

[RonaldB]

Pipk,

come on, spit out that humble pie ! That is by no means what i intended !
When i start nit-picking like that, it usually means i don't have any better suggestions...

Keep 'em coming !

Regards,
Ronald.

 

[pipk]

no offense Ronald, I was teasing....

 

[progolf069]

You guys, thanks for all of your help! I ended up using the idea of a tally method. There still is a few minor errors in the source code, but the actual extract data is exactly how I wanted it. Grouped by store number, and then by zip code, with all of the PO Boxes. You will pull a few non-zip-code addresses, just because they contain all of the letters in PO Box, but that is not going to be a big deal for our application. Thanks again for all of your help.

If you are interested here is the code that I ended up using (the entire cobol program!):

       IDENTIFICATION DIVISION.
       PROGRAM-ID. brdpob.
       AUTHOR. IAN P WICKLINE.
       INSTALLATION. K'S MERCHANDISE INC.
       DATE-WRITTEN. JUNE 2001.

      ******************************************************************
      * PRINTS REPORT FOR BRIDAL REGISTRY ADDRESS VERIFICATION. PART OF*
      * MONTHLY PROCESS FOR CUSTOMER SERVICE. SEE ALSO PROGRAM: brdadd *
      ******************************************************************

       ENVIRONMENT DIVISION.
       INPUT-OUTPUT SECTION.
       FILE-CONTROL.
           SELECT INPUT-FILE
               ASSIGN TO '/files/spooled/delete/brdout.txt'
           ORGANIZATION IS LINE SEQUENTIAL.
           SELECT OUTPUT-FILE
               ASSIGN TO '/files/spooled/delete/brdpob.txt'
           ORGANIZATION IS LINE SEQUENTIAL.
           SELECT SORT-FILE
               ASSIGN TO DISK.
       
       DATA DIVISION.

       FILE SECTION.
       FD  INPUT-FILE.
       01  INPUT-RECORD.
           05                              PIC X(132).
       SD  SORT-FILE.
       01  SORT-REC.
           05  SRT-STORE-NUM                PIC X(02).
           05  SRT-DATE                     PIC X(10).
           05  SRT-REG-NUM                  PIC X(08).
           05  SRT-PHONE                    PIC X(14).
           05  SRT-FIRST-NAME               PIC X(17).
           05  SRT-LAST-NAME                PIC X(19).
           05  SRT-ADDRESS                  PIC X(20).
           05  SRT-APPT-NUM                 PIC X(06).
           05  SRT-CITY                     PIC X(12).
           05  SRT-STATE                    PIC X(02).
           05  SRT-ZIP                      PIC X(05).
       FD  OUTPUT-FILE
           LABEL RECORDS ARE STANDARD.
       01  OUTPUT-RECORD.
           05  OUTPUT-REC                  PIC X(134).

       WORKING-STORAGE SECTION.
       01  ARE-THERE-MORE-RECORDS   PIC XXX VALUE 'YES'.
       01  SYSTEM-DATE.
           05  DATE-YEAR            PIC 9999.
           05  DATE-MONTH           PIC 99.
           05  DATE-DAY             PIC 99.
       01  REC-COUNTER              PIC 999.
       01  WS-ADDRESS.
           05  PO-ADDRESS           PIC X(20).
       01                   REDEFINES WS-ADDRESS.
           05  ADDRESS-CHAR OCCURS 10 TIMES PIC XX.
       01  CTR                      PIC 99.
       01  IS-PO-BOX-NUM            PIC 99.

       01  HEADING-REC-1.
           05                       PIC X(08) VALUE SPACES.
           05                       PIC X(16) VALUE "PROGRAM:  brdpob".
           05                       PIC X(06) VALUE SPACES.
           05                       PIC X(07) VALUE "DATE:  ".
           05  PRINT-DATE.
           05  PRINT-DATE.
               10  PRINT-MONTH      PIC 9(02).
               10                   PIC X(01) VALUE "/".
               10  PRINT-DAY        PIC 9(02).
               10                   PIC X(01) VALUE "/".
               10  PRINT-YEAR       PIC 9(04).
           05                       PIC X(05) VALUE SPACES.
           05                       PIC X(15) VALUE "BRIDAL REGISTRY".
           05                       PIC X(09) VALUE " ADDRESS ".
           05                       PIC X(13) VALUE "VERIFICATION ".
           05                       PIC X(13) VALUE "PO BOX REPORT".
           05                       PIC X(04) VALUE SPACES.
           05                       PIC X(14) VALUE "PAGE NUMBER:  ".
           05  PAGE-NUMBER          PIC 9(02).
       01  HEADING-REC-2.
           05                       PIC X(04) VALUE "STR#".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(08) VALUE "REGISTRY".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(10) VALUE "EVENT DATE".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(12) VALUE "PHONE NUMBER".
           05                       PIC X(03) VALUE SPACES.
           05                       PIC X(03) VALUE SPACES.
           05                       PIC X(10) VALUE "FIRST NAME".
           05                       PIC X(08) VALUE SPACES.
           05                       PIC X(09) VALUE "LAST NAME".
           05                       PIC X(11) VALUE SPACES.
           05                       PIC X(07) VALUE "ADDRESS".
           05                       PIC X(14) VALUE SPACES.
           05                       PIC X(07) VALUE "SUITE #".
           05                       PIC X(02) VALUE SPACES.
           05                       PIC X(04) VALUE "CITY".
           05                       PIC X(09) VALUE SPACES.
           05                       PIC X(05) VALUE "STATE".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(03) VALUE "ZIP".
       01  HEADING-REC-3.
           05                       PIC X(04) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(08) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(10) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(14) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(14) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(17) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(19) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(20) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(07) VALUE ALL "-".
           05                       PIC X(02) VALUE SPACES.
           05                       PIC X(12) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(05) VALUE ALL "-".
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(05) VALUE ALL "-".
       01  DETAIL-LINE.
           05  PRINT-STORE          PIC X(02).
           05                       PIC X(03) VALUE SPACES.
           05  PRINT-REGISTRY       PIC X(08).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-EVENT          PIC X(10).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-PHONE          PIC X(14).
           05                       PIC X(01) VALUE SPACES.
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-F-NAME         PIC X(17).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-L-NAME         PIC X(19).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-ADDRESS        PIC X(20).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-SUITE          PIC X(06).
           05                       PIC X(03) VALUE SPACES.
           05  PRINT-CITY           PIC X(12).
           05                       PIC X(01) VALUE SPACES.
           05  PRINT-STATE          PIC X(02).
           05                       PIC X(04) VALUE SPACES.
           05  PRINT-ZIP            PIC X(05).

       PROCEDURE DIVISION.
       000-START.
           SORT  SORT-FILE
                 ON ASCENDING KEY SRT-STORE-NUM
                                  SRT-ZIP
                    USING INPUT-FILE
                    OUTPUT PROCEDURE 100-INIT
           CLOSE OUTPUT-FILE
           CLOSE OUTPUT-FILE
           STOP RUN.

       100-INIT.
           OPEN OUTPUT OUTPUT-FILE
           MOVE 100 TO REC-COUNTER
           MOVE "0" TO PAGE-NUMBER
           MOVE FUNCTION CURRENT-DATE TO SYSTEM-DATE.
           MOVE DATE-YEAR TO PRINT-YEAR
           MOVE DATE-MONTH TO PRINT-MONTH
           MOVE DATE-DAY TO PRINT-DAY
           PERFORM UNTIL ARE-THERE-MORE-RECORDS = 'NO '
               RETURN SORT-FILE
                   AT END
                       MOVE 'NO ' TO ARE-THERE-MORE-RECORDS
                   NOT AT END
                       MOVE SRT-ADDRESS TO WS-ADDRESS
                       PERFORM VARYING CTR FROM 1 BY 1
                               UNTIL CTR > 10
                           IF ADDRESS-CHAR(CTR) = "PO" OR "BO"
                                                   OR "OX" OR
                                                   "P." OR "O."
                               ADD 1 TO IS-PO-BOX-NUM
                               ADD 1 TO IS-PO-BOX-NUM
                           END-IF
                       END-PERFORM
                       IF IS-PO-BOX-NUM > 1
                           PERFORM 200-PROCESS
                       END-IF
               END-RETURN
           MOVE ZEROS TO IS-PO-BOX-NUM
           END-PERFORM.

       200-PROCESS.
           IF REC-COUNTER >= 51
               ADD 1 TO PAGE-NUMBER
               MOVE ZEROS TO REC-COUNTER
               WRITE OUTPUT-RECORD FROM SPACES
                   AFTER ADVANCING PAGE
               PERFORM 300-HEADING-RTN
           END-IF
           PERFORM 400-PRINT-RTN.

       300-HEADING-RTN.
           WRITE OUTPUT-RECORD FROM SPACES
           MOVE HEADING-REC-1 TO OUTPUT-REC
           MOVE HEADING-REC-1 TO OUTPUT-REC
           WRITE OUTPUT-RECORD
           WRITE OUTPUT-RECORD FROM SPACES
           MOVE HEADING-REC-2 TO OUTPUT-REC
           WRITE OUTPUT-RECORD
           WRITE OUTPUT-RECORD FROM SPACES
           MOVE HEADING-REC-3 TO OUTPUT-REC
           WRITE OUTPUT-RECORD
           WRITE OUTPUT-RECORD FROM SPACES.

       400-PRINT-RTN.
           MOVE SRT-STORE-NUM TO PRINT-STORE
           MOVE SRT-DATE TO PRINT-EVENT
           MOVE SRT-REG-NUM  TO PRINT-REGISTRY
           MOVE SRT-PHONE TO PRINT-PHONE
           MOVE SRT-FIRST-NAME TO PRINT-F-NAME
           MOVE SRT-LAST-NAME TO PRINT-L-NAME
           MOVE SRT-ADDRESS TO PRINT-ADDRESS
           MOVE SRT-APPT-NUM TO PRINT-SUITE
           MOVE SRT-CITY TO PRINT-CITY
           MOVE SRT-STATE TO PRINT-STATE
           MOVE SRT-ZIP TO PRINT-ZIP
           WRITE OUTPUT-RECORD FROM DETAIL-LINE
           WRITE OUTPUT-RECORD FROM SPACES
           ADD 2 TO REC-COUNTER.