My experience with webfocus is somewhat limited. I came across something that I'm not sure is a bug in webfocus or not so I would like to know if anyone has ever experienced this before.

We have a SQL pass through statment with some field values and then we also have the same field value in a compute statement. When the results were printed in the report, they end result was sporadic. After investigating I was able to determine thate the column was displayed twice in the hold file.

Example:

SQL SQLORA PREPARE SQLOUT FOR
select amount, dr_cr_indicator
from table_name
where something = true
END

TABLE FILE SQLOUT
PRINT
COMPUTE AMOUNT/D22.2 = IF DEBIT_CREDIT_INDICATOR EQ 'D' THEN (AMOUNT * -1) ELSE AMOUNT;
END
ON TABLE HOLD AS TRANDET

If you do a Print * for TRANDET, you will see the amount column twice, once without the negative sign and once with the negative sign. I'm guessing that webfocus doesn't like the fact that the computed field has the same name as the field value.

Any thoughts or opinions from anyone are appreciated.

Thanks!

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

I would not treat that as a bug in WebFOCUS. It is *your code* which is creating new fields with the same name as those in the original source.

By default, WebFOCUS will propagate any field "used" in a request (regardless of whether or not it is being printed) to the HOLD file.

That means that the TRANDET HOLD file should have the following fields in it:

- AMOUNT (the one from the DB used in the COMPUTE expression)
- DEBIT_CREDIT_INDICATOR (also used in the COMPUTE expression)
- AMOUNT (you're computed one).

Even though they have the same name, each has a different alias (E01, E02, etc.) so you could pick the one you need using the alias name.

However, if you require not to have the original AMOUNT field in the HOLD, you can influence how WebFOCUS will do that by:

SET HOLDLIST = PRINTONLY

That way, only the compute'd AMOUNT will be in the HOLD file, because it is the only field that would show up in a report if you commented out the ON TABLE HOLD line in the request!

That's actually one of the default settings in my reporting environment but that's because of personal preference.

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

The PRINT * will print all fields in the SQLOUT hold file.

You have created another field called the same name, its as simple as that.

If you don't want the duplicate names, then specify the fields you need, or change the COMPUTE column name.

Whenever you have a COMPUTE, a row-based value, those columns within the COMPUTE will be included in the HOLD file. The same goes for CNT., CNT.DST., MAX., etc...
Additionally, COMPUTE is most often utilized with SUM; if you are going to PRINT, put it in a DEFINE...

Thanks for your replies. I figured it was related to duplicate field names and not a bug. I learn a little more every day!

Thanks again...

Try adding this to your TABLE FILE:

ON TABLE SET HOLDLIST PRINTONLY

EricH

Thanks for all the replies. I tested the ON TABLE SET HOLDLIST PRINTONLY and that took care of the duplicate fields.