Features Added in FOCUS 7.7.07

Example: Returning the Ceiling of a Number

In the following request, CEILING returns the smallest integer greater than or equal to the GROSS_PROFIT_US value.

DEFINE FILE WFLITE
CEIL1/D7.2= CEILING(GROSS_PROFIT_US);
END
TABLE FILE WFLITE
PRINT GROSS_PROFIT_US/D9.2  CEIL1
ON TABLE SET PAGE NOPAGE
END

The partial output follows. Note that even though the value returned is an integer, it is returned with the same format as the CEIL1 field (D7.2):

  Gross Profit     CEIL1      
  ------------     -----     
        165.00    165.00
         13.99     14.00
         60.99     61.00
        225.98    226.00
         79.99     80.00
         44.59     45.00
         94.30     95.00
        238.50    239.00
        199.99    200.00
         68.99     69.00
         63.58     64.00
        129.99    130.00
         37.49     38.00
         75.99     76.00
         13.99     14.00
        119.00    119.00
        -30.01    -30.00
         54.99     55.00
        189.98    190.00
         44.59     45.00
         91.98     92.00
         89.00     89.00
         59.50     60.00
        129.99    130.00
         54.00     54.00
        109.98    110.00
         98.99     99.00
         98.99     99.00
         99.99    100.00
         44.59     45.00

Example: Raising e to a Power

The following request prints the value of e and the value of e raised to the fifth power.

DEFINE FILE WFLITE
EXP1/D12.5 = EXPONENT(1);
EXP2/D12.5 = EXPONENT(5);
END
TABLE FILE WFLITE
PRINT EXP1 EXP2
BY BUSINESS_REGION AS Region
WHERE BUSINESS_REGION EQ 'EMEA'
WHERE RECORDLIMIT EQ 1
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Floor of a Number

In the following request, FLOOR returns the largest integer less than or equal to the GROSS_PROFIT_US value.

DEFINE FILE WFLITE
FLOOR1/D7.2= FLOOR(GROSS_PROFIT_US);
END
TABLE FILE WFLITE
PRINT GROSS_PROFIT_US/D9.2  FLOOR1
ON TABLE SET PAGE NOPAGE
END

Partial output follows. Note that even though the value returned is an integer, it is returned with the same format as the FLOOR1 field (D7.2):

  Gross Profit    FLOOR1       
  ------------    ------           
        165.00    165.00
         13.99     13.00
         60.99     60.00
        225.98    225.00
         79.99     79.00
         44.59     44.00
         94.30     94.00
        238.50    238.00
        199.99    199.00
         68.99     68.00
         63.58     63.00
        129.99    129.00
         37.49     37.00
         75.99     75.00
         13.99     13.00
        119.00    119.00
        -30.01    -31.00
         54.99     54.00
        189.98    189.00
         44.59     44.00
         91.98     91.00
         89.00     89.00
         59.50     59.00
        129.99    129.00
         54.00     54.00
        109.98    109.00
         98.99     98.00
         98.99     98.00
         99.99     99.00
         44.59     44.00

Example: Calculating the Remainder From a Division

In the following request, MOD returns the remainder of PRICE_DOLLARS divided by DAYSDELAYED:

DEFINE FILE WFLITE
MOD1/D7.2= MOD(PRICE_DOLLARS, DAYSDELAYED);
END
TABLE FILE WFLITE
PRINT PRICE_DOLLARS/D7.2  DAYSDELAYED/I5 MOD1
WHERE DAYSDELAYED GT 1
ON TABLE SET PAGE NOPAGE
ON TABLE PCHOLD FORMAT WP
END

Partial output follows:

Price    Days                      
   Dollars  Delayed      MOD1                 
   -------  -------      ----                                                                                                     
    399.00        3       .00
    489.99        3       .99
    786.50        2       .50
    599.99        4      3.99
     29.99        4      1.99
    169.00        2      1.00
    219.99        2      1.99
    280.00        3      1.00
     79.99        4      3.99
    145.99        2      1.99
    399.99        3       .99
    349.99        3      1.99
    169.00        3      1.00

Example: Raising a Base Value to a Power

In the following request, POWER returns the value COGS_US/20.00 raised to the power stored in DAYSDELAYED:

DEFINE FILE WFLITE
BASE=COGS_US/20.00;
POWER1= POWER(COGS_US/20.00,DAYSDELAYED);
END
TABLE FILE WFLITE
PRINT BASE IN 15 DAYSDELAYED POWER1
BY PRODUCT_CATEGORY
WHERE PRODUCT_CATEGORY EQ 'Computers'
WHERE DAYSDELAYED NE 0
ON TABLE SET PAGE NOPAGE
END

Partial output follows:

  Product                              Days         
  Category                BASE         Delayed          POWER1  
  --------                ----         -------          ------  
  Computers              12.15               3        1,793.61
                         16.70               2          278.89
                          8.35               1            8.35
                          8.10               2           65.61
                          4.05               1            4.05
                          4.05               2           16.40
                          4.05               4          269.04
                          8.35               1            8.35
                         16.70               1           16.70
                          8.35               3          582.18
                          8.35               1            8.35
                          4.05               1            4.05
                          4.05               1            4.05
                          8.35               4        4,861.23
                          8.35              -1             .12
                          8.35               1            8.35
                          8.35               3          582.18

Example: Retrieving the Last Non-Blank Value

The following request shows the SALARY field, the LAST_NONBLANK value from a DEFINE, and the LAST_NONBLANK value from a COMPUTE.

DEFINE FILE EMPLOYEE
MYSAL/I5 MISSING ON=IF DEPARTMENT EQ 'MIS' THEN SALARY ELSE MISSING;
LAST_NONBLANK1/I5 MISSING ON= LAST_NONBLANK(MYSAL);
END
TABLE FILE EMPLOYEE
PRINT LAST_NAME MYSAL  LAST_NONBLANK1
COMPUTE LAST_NONBLANK2/I5 MISSING ON=LAST_NONBLANK(MYSAL);
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
END

The output is shown in the following image.

Example: Matching a String Against a Regular Expression

The following request matches the FIRSTNAME field against the regular expression '^Sara(h?)$', which matches Sara or Sarah:

TABLE FILE WFLITE
PRINT FIRSTNAME AND COMPUTE
REG1/I1=REGEX(FIRSTNAME,'^Sara(h?)$') ;
BY LASTNAME/A10
WHERE LASTNAME EQ 'Allen'
END

The output is

              First           
  LASTNAME    Name                  REG1    
  --------    -----                 ----           
  Allen       Penny                    0
              Rosemary                 0
              Amber                    0
              Julie                    0
              Sarah                    1
              Leo                      0
              Margret                  0
              Donna                    0
              Damian                   0
              Alexander                0
              Diego                    0
              Amber                    0
              Susan                    0
              Amber                    0
              Sara                     1
              Sara                     1

Example: Concatenating Strings After Removing Blanks From the First

The following request concatenates city names with state names. Note that the city and state names are converted to fixed length alphanumeric fields before concatenation.

DEFINE FILE WFLITE
CITY/A50 = CITY_NAME;
STATE/A50 = STATE_PROV_NAME;
CONCAT_CS/A100 = CONCAT(CITY,STATE);
END  
 
TABLE FILE WFLITE
SUM CITY AS City STATE AS State CONCAT_CS AS Concatenation
BY STATE_PROV_NAME NOPRINT
WHERE COUNTRY_NAME EQ 'United States'
WHERE STATE LE 'Louisiana'
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Replacing a String

REPLACE replaces the string 'South' in the Country Name with the string 'S.'

SET TRACEUSER = ON 
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP=OFF
DEFINE FILE WFLITE
NEWNAME/A20 = REPLACE(COUNTRY_NAME, 'SOUTH', 'S.');
END
TABLE FILE WFLITE
SUM COUNTRY_NAME
BY NEWNAME AS 'New,Name'
WHERE COUNTRY_NAME LIKE 'S%'
ON TABLE SET PAGE NOLEAD
END

The generated SQL passes the REPLACE function to the DBMS REPLACE function.

SELECT   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.'),  
MAX(T3."COUNTRY_NAME")  
FROM   
wrd_wf_retail_geography T3  
WHERE   
(T3."COUNTRY_NAME" LIKE 'S%')  
GROUP BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.')  
ORDER BY   
REPLACE(T3."COUNTRY_NAME",'SOUTH','S.'); 

The output is shown in the following image.

Example: Replacing All Instances of a String

In the following request, the virtual field DAYNAME1 is the string DAY1 with all instances of the string 'DAY' replaced with the string 'day'. The virtual field DAYNAME2 has all instances of the string 'DAY' removed.

DEFINE FILE WFLITE
DAY1/A30 = 'SUNDAY MONDAY TUESDAY';
DAYNAME1/A30 = REPLACE(DAY1, 'DAY', 'day' );
DAYNAME2/A30 = REPLACE(DAY1, 'DAY', '' );
END
TABLE FILE WFLITE
PRINT DAY1 OVER
DAYNAME1 OVER
DAYNAME2
WHERE EMPLOYEE_NUMBER EQ 'AH118' 
ON TABLE SET PAGE NOPAGE
END

The output is:

  DAY1      SUNDAY MONDAY TUESDAY         
  DAYNAME1  SUNday MONday TUESday         
  DAYNAME2  SUN MON TUES                  

Example: Extracting an Element From a String

The following request defines strings and extracts elements from them.

DEFINE FILE WFLITE
STRING1/A50 WITH COUNTRY_NAME= 'http://www.informationbuilders.com';
STRING2/A20 = 'user1@ibi.com';
STRING3/A20 = 'Louisa May Alcott';
Protocol/A20 = SPLIT(URL_PROTOCOL, STRING1);
Path/A50 = SPLIT(URL_PATH, STRING1);
Domain/A20 = SPLIT(EMAIL_DOMAIN, STRING2);
User/A20 =  SPLIT(EMAIL_USERID, STRING2);
First/A10 = SPLIT(NAME_FIRST, STRING3);
Last/A10 = SPLIT(NAME_LAST, STRING3);
END
TABLE FILE WFLITE
SUM Protocol Path User Domain First Last
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Using the CHAR Function to Insert Control Characters Into a String

The following request defines a field with carriage return (CHAR(13)) and line feed (CHAR(10)) characters inserted between the words HELLO and GOODBYE (in an ASCII environment). To show that these characters were inserted, the output is generated in PDF format and the StyleSheet attribute LINEBREAK='CRLF' is used to have these characters respected and print the field value on two lines.

DEFINE FILE WFLITE
MYFIELD/A20 WITH COUNTRY_NAME='HELLO' | CHAR(13) | CHAR(10) | 'GOODBYE';
END
TABLE FILE WFLITE
SUM MYFIELD 
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,LINEBREAK='CRLF',$
ENDSTYLE
END

The output is shown in the following image.

Example: Using the CTRLCHAR Function to Insert Control Characters Into a String

The following request defines a field with carriage return (CTRLCHAR(CR)) and line feed (CTRLCHAR(LF)) characters inserted between the words HELLO and GOODBYE. To show that these characters were inserted, the output is generated in PDF format and the StyleSheet attribute LINEBREAK='CRLF' is used to have these characters respected and print the field value on two lines.

DEFINE FILE WFLITE
MYFIELD/A20 WITH COUNTRY_NAME='HELLO' | CTRLCHAR(CR) | CTRLCHAR(LF) | 'GOODBYE';
END 
TABLE FILE WFLITE
SUM MYFIELD 
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT,LINEBREAK='CRLF',$
ENDSTYLE
END

The output is shown in the following image.

Example: Converting a Date to a Character String

The following request defines a date field as YYMD format and converts it to a character string (CharDate) in YYMtrD format.

DEFINE FILE WFLITE
DATE1/YYMD = TIME_DATE_DAY_COMPONENT;
CharDate/A20 = EDIT2(DATE1,'YYMtrD');
END
TABLE FILE WFLITE
SUM COGS_US
BY CharDate
WHERE TIME_MTHNAME  EQ 'FEB'
ON TABLE SET PAGE NOLEAD
END

The output is shown in the following image.

Example: Returning a Hexadecimal View

The following request returns a hexadecimal view of the country names and the sum of the days delayed.

DEFINE FILE WFLITE
Days/I8 = DAYSDELAYED;
Country/A20 = COUNTRY_NAME;
HexCountry/A30 = HEXTYPE(Country);
END
TABLE FILE WFLITE
SUM COUNTRY_NAME NOPRINT  Country HexCountry Days
COMPUTE HexDays/A40 = HEXTYPE(Days);
BY COUNTRY_NAME NOPRINT
WHERE COUNTRY_NAME LT 'P'
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Generating a Phonetic Key

The following request changes the spelling of the last name for MARY SMITH to SMYTHE and generates a phonetic key for each last name.

DEFINE FILE EMPLOYEE
LAST_NAME2/A16 = IF LAST_NAME EQ 'SMITH' AND FIRST_NAME EQ 'MARY' THEN 'SMYTHE' ELSE LAST_NAME;
PKEY/A10 = PHONETIC(LAST_NAME2);
END
TABLE FILE EMPLOYEE
PRINT FIRST_NAME LAST_NAME2 
BY PKEY
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image. Note that the two spellings for SMITH are assigned the same index number.

Example: Converting a Character String to an Integer Value

The following request converts character strings to integers. Digits following the decimal point are truncated.

DEFINE FILE WFLITE
INT1/I8 = TO_INTEGER('56.78');
INT2/I8 = TO_INTEGER('.5678');
INT3/I8 = TO_INTEGER('5678');
END
TABLE FILE WFLITE
PRINT INT1 INT2 INT3
BY BUSINESS_REGION AS Region
WHERE READLIMIT EQ 1
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Example: Converting a Character String to a Number

The following request converts character strings to double-precision floating point numbers.

DEFINE FILE WFLITE
NUM1/D12.1 = TO_NUMBER('56.78');
NUM2/D12.2 = TO_NUMBER('0.5678');
END
TABLE FILE WFLITE
PRINT NUM1 NUM2
BY BUSINESS_REGION AS Region
WHERE READLIMIT EQ 1
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF,$
ENDSTYLE
END

The output is shown in the following image.

Example: Returning the Current Date

The following request returns the current date.

DEFINE FILE WFLITE
CURRDATE/YYMD WITH COUNTRY_NAME = DT_CURRENT_DATE();
END
TABLE FILE WFLITE
SUM CURRDATE
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Current Date and Time

The following request returns the current date and time, with the time specified in microseconds.

DEFINE FILE WFLITE
CURRDATE/HYYMDm WITH COUNTRY_NAME = DT_CURRENT_DATETIME(MICROSECOND);
END
TABLE FILE WFLITE
SUM CURRDATE
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Returning the Current Time

The following request returns the current time, with the time precision set to milliseconds.

DEFINE FILE WFLITE
CURRTIME/HHISs WITH COUNTRY_NAME = DT_CURRENT_TIME(MILLISECOND);
END
TABLE FILE WFLITE
SUM CURRTIME
ON TABLE SET PAGE NOPAGE
END

The output is shown in the following image.

Example: Encrypting a Password

The following request encrypts the value guestpassword using the encryption algorithm configured in FOCUS.

-SET &P1 = ENCRYPT('guestpassword');
-TYPE &P1

The returned encrypted value is {AES}963AFA754E1763ABE697E8C5E764115E.

Example: Retrieving the Value of an Environment Variable

The following request retrieves the value of the variable EDAEXTSEC.

-SET &E1 = GETENV('EDAEXTSEC');
-TYPE &E1

The value returned is ON if FOCUS was started with security on or OFF if FOCUS was started with security off.

Example: Assigning a Value to the UNIX PS1 Variable

The following request assigns the value FOCUS/Shell: to the UNIX PS1 variable.

-SET &P1 = PUTENV('PS1','FOCUS/Shell:');

This causes UNIX to display the following prompt when the user issues the UNIX shell command SH:

FOCUS/Shell:

The following request creates a variable named xxxx and sets it to the value this is a test. It then retrieves the value using GETENV.

-SET &XXXX=PUTENV(xxxx,'this is a test');
-SET &YYYY=GETENV(xxxx);
-TYPE  Return Code: &XXXX,  Variable value: &YYYY

The output is:

Return Code: 0,  Variable value: this is a test

Example: Sample LNGPREP Command

Assume the lnglist.cfg file contains the language codes fre (French) and spa (Spanish):

fre
spa

Issue the following LNGPREP command:

LNGPREP FILE weather/forecast LNGAPP xlate LNGPREFIX tq_ LNGFILE  xlate/lnglist

The following language files will be created:

• xlate / tq_eng.lng
• xlate / tq_fre.lng
• xlate / tq_spa.lng

The Master File weather/ forecast.mas will be updated with the following attribute

TRANS_FILE= xlate/tq_

Translators then have to translate the values in xlate/tq_fre.lng and xlate/tq_spa.lng.