I am looking to create a function that looks up and returns data from one of our tables. For example, using the CAR file, I would like to have a function called MAX_RETAIL_COST with an input parameter being COUNTRY. It would then return the MODEL that has the highest RETAIL_COST of all the cars in that COUNTRY. Is this possible? How would it be defined? And how would it be called in another fex.

MAX_RETAIL_COST('ENGLAND') would return 'INTERCEPTOR III'

Thanks

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

DEFINE'd FUNCTIONS are a set of expressions that operate on a single value instance (record-level or Dialogue Manager variable) at a time, therefore they are not intended to be used to perform aggregations or operate over a set of records to return a single value.

You could however, create a HOLD file with all the aggregations you need grouped by a "key" (in your example, COUNTRY).

In the "body" of your MAX_RETAIL_COST function you could then look up the appropriate column in that HOLD file by the current "COUNTRY".

Search in the documentation for DB_LOOKUP to get some ideas.

This is probably what you would end up doing:

TABLE FILE CAR
SUM MAX.RETAIL_COST AS MAX_RETAIL_COST
    MIN.RETAIL_COST AS MIN_RETAIL_COST
    MAX.DEALER_COST AS MAX_DEALER_COST
    MIN.DEALER_COST AS MIN_DEALER_COST
    RETAIL_COST     AS SUM_RETAIL_COST
    ... other measurements here ...
BY COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HCNTRYSUM
END
-*
DEFINE FUNCTION MAX_RETAIL_COST (CNTRY/A10)
MAX_RETAIL_COST/D20.2 = DB_LOOKUP(HCNTRYSUM, COUNTRY, CNTRY, MAX_RETAIL_COST);
END
-*
TABLE FILE CAR
PRINT CAR
      MODEL
COMPUTE MAX_RTL_COST/D20 = MAX_RETAIL_COST(COUNTRY);
      ... other stuff ...
BY COUNTRY
END

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

Well done njsden, thats what I was going to suggest.



DB_LOOKUP is a little used, but powerful function.

Hello,

i tried to play a little bit with the code. But i got an error that the field country is not recognized!! I just copied and pasted the code and deleted the ... other measurements here ...
and ... other stuff ... rows.

Regards

Christian

I had thh same problem. Since I am new to DB_LOOKUP, I tried it first in a DEFINE FILE instead of DEFINE FUNCTION just to make sure I understand the basics of it. However, when I tried to run it, it crashed. I have not yet had time to debug it yet.

quote:

Search in the documentation for DB_LOOKUP

ChristianP, the code mock-up I provided was just to illustrate the idea of what could be done but not intended to be run as it was (it wouldn't anyway as there is stuff there that is not really "code"). That's why I suggested that documentation on DB_LOOKUP be searched (and implicitly analyzed and tested).

quote:

country is not recognized

Based on the error message you got, it seemed obvious to me that the reference to COUNTRY in the function was not being recognized. By quickly looking at documentation on DB_LOOKUP I realized that 2 of its parameters had been swapped.

This adjusted piece of code works in 7.7.03:

TABLE FILE CAR
SUM MAX.RETAIL_COST AS MAX_RETAIL_COST
    MIN.RETAIL_COST AS MIN_RETAIL_COST
    MAX.DEALER_COST AS MAX_DEALER_COST
    MIN.DEALER_COST AS MIN_DEALER_COST
    RETAIL_COST     AS SUM_RETAIL_COST
BY COUNTRY
ON TABLE SET ASNAMES ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS HCNTRYSUM
END
-RUN
TABLE FILE HCNTRYSUM
PRINT *
END
-*
DEFINE FUNCTION MAX_RETAIL_COST (CNTRY/A10)
MAX_RETAIL_COST/D20.2 = DB_LOOKUP(HCNTRYSUM, CNTRY, COUNTRY, MAX_RETAIL_COST);
END
-*
TABLE FILE CAR
PRINT CAR
      MODEL
COMPUTE MAX_RTL_COST/D20 = MAX_RETAIL_COST (COUNTRY);
BY COUNTRY
END

VinceCSW: I used DB_LOOKUP for the first time in WF 7.6.2 so I don't expect any issues using it in your current version.

I do however suggest that this function be not used without first reading its documentation, understanding what it does, which cases it's best suitable for and, most importantly, testing, testing, and testing it with different parameters and "file sizes" to get a better feeling about its functionality and performance. You may discover that sometimes you'd be better off implementing a JOIN instead of performing a row-by-row look-up but as with many other things, "it depends" That's why testing is crucial.


Hope that helps.

It sure did help. With what you have shown, I will be able to do exactly what I need to. Thanks a lot.

Hi njsden,

i just look every day through the forum to find stuff what i can use in the future. So thanks a lot and i will read the documentation!!

Regards

Christian

ChristianP, I think I owe you an apology. Reading through my post I noticed the "tone" of my message being a bit rude and that was not really my intention. Unfortunately, I had just finished reading and responding to some individual's unprofessional posting at Focal Point and my mood was shaken.

The part about reading the documentation about any new function we want to use in our development still makes sense and that's exactly what you do by coming here ... at the end of the day, Focal Point is also a source of information that complements "formal" documentation, is it not?

quote:

i just look every day through the forum to find stuff what i can use in the future

I think that describes many of us here and I'm sure that in many cases you come here not only to learn but also to contribute so your ideas and suggestions may benefit others in their learning from your experiences and that's something I certainly appreciate.

So once again, please accept my apologies for the "un-politeness" (okay, okay, rudeness ) of my previous comment and keep up the WebFOCUSing and learning!