Hi everyone, Please I need some help from you guys Here is the deal, I don't understand very well the use of DEFINE command in FOCUS, I've read that define goes after a JOIN and before TABLE FILE command. What is the relation betwwen DEFINE and TABLE FILE, what's the goal of declaring the DEFINE section.
Thanks a lot!

This message has been edited. Last edited by: Kerry,

A define creates data source level temporary fields, therefore they are related to each record in the source file.

This way you can do calculations at row level. this is prior to any FOCUS sorting aggregation, etc.

And yes, the DEFINE usually goes between JOIN and TABLE. By default, JOIN clears any DEFINE set for the host file (the first one mentioned in the join). But, by issuing a SET KEEPDEFINES=ON command you can change that behavior, JOIN in this case will not destroy any existing defines.

Waz is, as always, spot on.

When you have created a defined field it can be used for both selection (filtering) as well as sorting.

But maybe a practical example is what you are looking for. I have a situation where I need to associate sales reps with given customers. Mostly a sales rep is responsible for a whole country, but sometimes a sales rep will be responsible for a single customer in another guy's general territory. Then we have a special situation where one sales group is responsible only for a certain line of products (HII)that completely overlaps another guy's general territory - plus one member of that group is in a non-compete situation because of previous employment.

With this (condensed)DEFINE I'm able to associate the correct sales rep with each customer.

How do I use it? : Every day a report is run against the Sales database to identify new customers and a hold file is created. I use the results of that hold file in a MODIFY routine that updates a SalesRep cross-reference database. That database is subsequently used in all manner of reports using the JOIN command, which saves me from incorporating this define in every report.

DEFINE FILE WHATEVER
SALESREP/A3 = IF COUNTRY EQ 'DR' OR 'EC' OR 'NI' OR 'PA' THEN 'AM'
   ELSE IF COUNTRY EQ 'AN' OR 'BH' THEN 'BD'
   ELSE IF COUNTRY EQ 'AG' OR 'BO' OR 'BR' OR 'BZ' OR 'CO' OR 'CR' OR 'GU' OR 'HO' OR 'MX' OR 'PR' OR 'SA' OR 'UR' THEN 'CB'
   ELSE IF COUNTRY EQ 'US' THEN 'MR' ELSE '???';

SALESREP = IF CCODE EQ 'JADI' OR 'USASP' OR 'USSCRT' THEN 'BD' ELSE
           IF CCODE EQ 'USMC' THEN 'RT' ELSE SALESREP;

HIIFLAG/A1 = EDIT(CCODE, '$$9$$$');
LAST3CHARS/A3 = IF HIIFLAG EQ '6' THEN (EDIT(CCODE, '$$$999')) ELSE ' ';
HIIREP/A3 = IF LAST3CHARS EQ 'STE' OR 'SYS' OR 'PRC' OR 'FAL' OR 'KES' OR 'REX' OR 'PRI'
   OR 'SAN' OR 'HAC' OR 'KRL' OR 'PHA' OR 'COF' OR 'ORI' OR 'CIP' OR 'ALL' THEN 'FA' ELSE 'MP';

SALESREP = IF HIIFLAG EQ '6' THEN HIIREP ELSE SALESREP;

END

My second example shows a few more uses of DEFINE (just scratching the surface really).

The first line shows how to create a new constant (which I incorporate into a database in this case). The next (PO) shows how to increase the length of an incoming data field, which I needed to match some existing data. The PTERM expression shows how to use an EDIT mask to extract an alpha value from a string and associate it with an integer - in this case for aging of accounts receivables.
Finally, the OSTAT expression uses the DECODE function to take an incoming alpha value and return a different alpha value to the same variable name.

DEFINE FILE CSOHCI
    DEPT/A1 = 'D';
    PO/A15 = PO;
    PTERM/I3 = IF EDIT(LONG_TERMS, '$$$$99$$$$$$') EQ '30' THEN 30
               ELSE IF EDIT(LONG_TERMS, '$$$$99$$$$$$') EQ '60' THEN 60
        ELSE IF EDIT(LONG_TERMS, '$$$$99$$$$$$') EQ '90' THEN 90
        ELSE IF EDIT(LONG_TERMS, '$$$$999$$$$$') EQ '120' THEN 120
        ELSE IF EDIT(LONG_TERMS, '99$$$$$$$$$$') EQ '45' THEN 45
        ELSE 0;
    OSTAT = DECODE OSTAT('1' 'Y' '3' 'D' '5' 'C' '7' 'Y' '9' 'X');
END

DEFINE is one of the most powerful instuments in the WebFOCUS toolbox and is much easier in many cases than figuring out a complicated COMPUTE or WHERE clause. And note that it doesn't always come after a JOIN - that's only the case where the final output report is expected next. So you could have a DEFINE and run a report with a HOLD followed by a JOIN and another DEFINE followed by a MATCH FILE or a MODIFY or a Maintain or a final report. The possibilities are endless.

One thing I would advise: Get used to typing your own code rather than using the GUI - it's much easier !

George

Thank you guys for having the time to share his knowledge with me, I'm still training to learn the FOCUS language the easy way.
Regards!