If you have ever interviewed for a WebFOCUS developer position, you have heard this: "What is the difference between DEFINE and COMPUTE?" It is a simple question, but surprisingly there are many who cannot articulate a good response.

This question serves the same purpose as the college English 101 class. It immediately knocks out people who should not even be sitting in the room.

Both DEFINE and COMPUTE are phrases used in the WebFOCUS 4GL computer language to perform calculations. These are robust commands that can be logical (IF-THEN-ELSE phrase), mathematical (such as calculating percentages), comparisons, or calls to other programs (such as user-written subroutine).

So if both do calculations, why are there two of them?

When you create a calculation using the DEFINE statement, WebFOCUS will perform it on every record that meets your selection criteria (an IF or WHERE test). So if you have one million sales transactions and are aggregating them by month, the DEFINE calculation will happen one million times.

On the other hand, WebFOCUS performs the COMPUTE calculations only after its sorting and aggregation steps have finished. So WebFOCUS would first sum your one million records into twelve months and then perform the COMPUTE twelve times.

The DEFINE happens on every detail database record while the COMPUTE is on each aggregated result line. Your next question is, when would you use one over the other?

The common response deals with efficiencies. If you can get the same results performing a calculation one million times versus doing it just twelve times, you want to use the one with the least amount of work involved. That makes your BI reports run faster and more efficiently.

Another common but less valid response is: "if you get the same results either way, just always use DEFINE." That is the non-thinking person's answer.

The real answer is you use either COMPUTE or DEFINE when you must. Sometimes, you must be at the detail record level in order to perform a calculation. Sometimes, you must work with the aggregated values.

One example would involve percentages. You can never calculate ratios and then add them together (at least, not if you want to show correct figures).

Take these three students, their individual grades, and their very small class total:

If you were to just sum the three detail averages to get a class total, you would get 268% (96% + 92% + 80%), which is not correct. Therefore, you would not use a DEFINE statement to calculate these percentages and then sum them together--you would get the wrong ratio.

Instead, you could use a COMPUTE phrase to summarize the details by class and then calculate the results. The COMPUTE will provide you with the summary figures of 67 divided by 75 for the right ratio of 89.3%.

Of course, handling this single interview topic properly will not get you that WebFOCUS job. It will, however, give you a chance to answer more than one question.

This question serves the same purpose as the college English 101 class. It immediately knocks out people who should not even be sitting in the room.

Both DEFINE and COMPUTE are phrases used in the WebFOCUS 4GL computer language to perform calculations. These are robust commands that can be logical (IF-THEN-ELSE phrase), mathematical (such as calculating percentages), comparisons, or calls to other programs (such as user-written subroutine).

So if both do calculations, why are there two of them?

When you create a calculation using the DEFINE statement, WebFOCUS will perform it on every record that meets your selection criteria (an IF or WHERE test). So if you have one million sales transactions and are aggregating them by month, the DEFINE calculation will happen one million times.

On the other hand, WebFOCUS performs the COMPUTE calculations only after its sorting and aggregation steps have finished. So WebFOCUS would first sum your one million records into twelve months and then perform the COMPUTE twelve times.

The DEFINE happens on every detail database record while the COMPUTE is on each aggregated result line. Your next question is, when would you use one over the other?

The common response deals with efficiencies. If you can get the same results performing a calculation one million times versus doing it just twelve times, you want to use the one with the least amount of work involved. That makes your BI reports run faster and more efficiently.

Another common but less valid response is: "if you get the same results either way, just always use DEFINE." That is the non-thinking person's answer.

The real answer is you use either COMPUTE or DEFINE when you must. Sometimes, you must be at the detail record level in order to perform a calculation. Sometimes, you must work with the aggregated values.

One example would involve percentages. You can never calculate ratios and then add them together (at least, not if you want to show correct figures).

Take these three students, their individual grades, and their very small class total:

**Bob:**24 out of 25 questions (24/25 = 96%)**Ted:**23 out of 25 questions (23/25 = 92%)**Sue:**20 out of 25 questions (20/25 = 80%)*Class Total: 67 out of 75 (67/75 = 89.3%)*

If you were to just sum the three detail averages to get a class total, you would get 268% (96% + 92% + 80%), which is not correct. Therefore, you would not use a DEFINE statement to calculate these percentages and then sum them together--you would get the wrong ratio.

Instead, you could use a COMPUTE phrase to summarize the details by class and then calculate the results. The COMPUTE will provide you with the summary figures of 67 divided by 75 for the right ratio of 89.3%.

Of course, handling this single interview topic properly will not get you that WebFOCUS job. It will, however, give you a chance to answer more than one question.

## No comments:

Post a Comment