Bakken Software Services

Specializing in the Pick / MultiValue Database & Environments

Home

Consulting

Education

Education Overview

Education - Schedule

Introduction to Pick / MV

Database Retrieval - Fund

Database Retrieval - Adv

Basic Programming

Enrollment

RPL

Tips & Tricks

Join Our Mailing List

Contact Us

Tips & Tricks - Index / Dcount
This combination of statements will allow you to search a multivalued field for a match of a partial string.

Let's say that you have a multi-valued field of telephone numbers in a customer record, and as has happened so frequently in the last few years, new area codes have been assigned to specific telephone exchanges. In order to update and change the area code, the INDEX/DCOUNT combo might be useful.

For this example, we will need to find telephone numbers with an area code of 708 and an exchange of 351 and then change the area code to 630.

 Atb  Data                                              
 1  Bakken Software  Company Name
 2  358 Army Trail Road #140 - 181  Street Address
 3  Bloomingdale  City
 4  IL  State
 5  60108  Zipcode                                                                                        
 6  800/742-5911]708/351-1052]708/351-1802  Telephone
   (multivalue mark identified in red above)  
     Atb. 6 contains three telephone numbers in a multivalued field, as listed below
     800/742-5911
     708/351-1052
     708/351-1802
     

 
One method of identifying the area codes that need to be updated might be to:

1) Determine how many values exist within attribute 6
2) Loop through each of the values, parsing apart the telephone number
3) Checking each area code to see if the area code is to be changed

This would definitely work, but a more efficient method would be to use the INDEX/DCOUNT combo.
POS  =  INDEX(CUST.REC<6>,"708/351",1)  The INDEX function will search the string of                                 
   800/742-5911]708/351-1052]708/351-1802  for the 1st occurence of 
   the substring or partial value of 708/351
   Format of INDEX:    
    var = INDEX(STRINGTOSEARCH , SUBSTRING , OCCURRENCE)
   In our example to the left, POS will be set to 14 as the substring
   708/351 starts at the 14th character in the string
   800/742-5911]708/351-1052]708/351-1802 
                     14th character
   
MV.POS = DCOUNT(CUST.REC<6>[1,POS],CHAR(253))  The DCOUNT function will count the number of values separated
   by the specified separator (multi-value mark ASCII CHAR 253)
   starting with the 1st through the 14th character of the string
   The result will be stored in the variable MV.POS                       
   800/742-5911]708/351
  2 values separated by a multi-value mark
  MV.POS will equal 2
   

We now know that the 1st occurrence of the area code to be changed exists as the 2nd multi-valued field in attribute 6 of the
customer record, and would need to now make the necessary change.

As this is a multi-valued field and more than one value might fit the condition under which the area code will need to be changed, we most likely would include this code within some type of loop in order to insure that all the telephone numbers needing to be updated, are indeed updated.

The full segment of code might look like the following:


LOOP  Begin looping through data to find all occurrences
    POS = INDEX(CUST.REC<6>,"708/351",1)  Find the character position of the substring if it exists
    MV.POS = DCOUNT(CUST.REC<6>[1,POS],CHAR(253))  Find the multivalue position of the substring within the attribute 
 UNTIL MV.POS = 0 DO  As long as the substring is found, do the following   
    PHONE = CUST.REC<6,MV.POS>  Extract the full telephone number to be changed from the atb.
    SUFFIX = OCONV(PHONE,"G1-1")  Parse apart the phone# to obtain the suffix
    CUST.REC<6,MV.POS> =  "630/351-":SUFFIX  Replace data existing telephone# with new telephone#
 REPEAT  Repeat process - check if another occurrence exists 
   
   This example is shown using a dynamic array for the customer
   information. Obviously a READU statement should be used prior
   to this segment of code in order to lock the record while updating,
   and a WRITE statement needs to follow the segment of code in order
   to update the customer file with the updated telephone numbers
   

Here's another example of how this combination of statements might come in handy. Remember, this is used for searching  a multivalued field for a match of a partial string.

In our customer file we might have a multi-valued field of open invoice information contained within the customer record, and to make things a bit more complicated, not only is the invoice# within a multi-valued field, it is also concatenated to the invoice date in which is in internal format.

(and yes, there probably is a better structure for this data, but this is from an actual client's site for which BSS was responsible for the maintenance of  existing software and had come on board long after the original software was written.)


 Atb  Data                                              
 1  Bakken Software  Company Name
 2  358 Army Trail Road #140 - 181  Street Address
 3  Bloomingdale  City
 4  IL  State
 5  60108  Zipcode                                                                                        
 6  630-351-1052  Telephone
 7  14701*100120]14722*100227]14750*100331  Invoice Date * Invoice# - bracket in red denotes the mv mark
     Atb. 7 contains three multivalued fields, as listed below
     14701*100120   (invoice date 03/31/08  Invoice# 100120)
     14722*100227   (invoice date 04/21/08  Invoice# 100227)
     14750*100331   (invoice date 05/19/08  invoice# 100331)

 
If we needed to search for a particular invoice#, it might be a bit cumbersome, as we can not use the LOCATE statement, as the
LOCATE statement will look for an exact match to the string that we are searching with.

One method might be to:

1) determine the number of multivalues
2) loop through the multivalues parsing apart the invoice date and invoice number
3) compare each invoice# in the multivalued field to the invoice# that we are searching for

This would work, or we can use the INDEX/DCOUNT combo. But, if I may be so bold as to quote Lee Burstein "Know your data."

In our example, we know that our values in the multi-valued field are the invoice date in internal format concatenated to the invoice number with an asterisk, and in sorted order by the invoice date. And we know this NOT by looking at this individual record, but by knowing that this is how the application software is building the data.


Where the invoice# to be found is 100227 and in variable INVOICE.NO                                           
   
 POS  =  INDEX(CUST.REC<7>,INVOICE.NO,1)  POS wil = 20 which is the character position of the start
   of the invoice# in the multivalued string
   14701*100120]14722*100227]14750*100331
                              20th character
   
 MV.POS = DCOUNT(CUST.REC<7>[1,POS],CHAR(253))  The DCOUNT will count the number of values separated
   by the multivalue mark (ASCII 253) starting with the 1st
   through the 20th character of the string
   14701*100120]14722*1
   MV.POS will equal 2
   

For those of you familiar with RPL (Realtime Programming Language), a product of SMI (Systems Management Inc., or Realtime Software), this is similar to the MVL statement with a wild card.

Note: the INDEX/DCOUNT combo is only a suggested format of searching for a partial match in a multivalued field, as opposed to looping through all the values and comparing each value. This technique may not apply to your data structure and should never be used when LOCATE statements would suffice. And as always, "Know your data."

If the multivalued field did NOT include the invoice date, and was only a multivalued field of invoice numbers, the best method
of searching would be to use the LOCATE statement as it searches the attribute looking at each multi value to find the multi-value
that you are searching for.

LOCATE(INVOICE.NO,CUST.REC,7;MV.POS;'AR') THEN
    *  found it
END ELSE
   *  returns the position it should be in if the value were to be inserted
   * and this example is using the AR, ascending right for the sort sequence
END

Bakken Software Services ... The Obvious Pick
 1-708-998-4925