Hi all,

We have found a problem that only shows its ugly face when the report is run via the Report Caster!

Quite a few of our complex reports use a technique that has worked faultlessly for a long time….

If a user enquires on a certain field in a report we sometimes have to first build a 'cut down' version of our main driving file, before entering the standard joins and report.

For example an Order report will usually drive from the main ORDER file and link to CUSTOMER, PRODUCT etc.

However, if a user enquires on a 'Product Campaign Code' we enter a special process that eventually gives us a file that contains all the Orders and Order Information that belong to that Campaign. We create a HOLD file with this data and name it the same as our ORDERS file.

We then leave this special section and continue with the standard report, but the program drives from our 'cut down' ORDERS file instead of the normal file.

This saves us from having to change the main report code. All the file names remain the same and we are still able to use TABLE FILE ORDERS, instead for example many IF statements asking if this is a standard enquiry or a campaign enquiry and for us having to use a separate ‘TABLE FILE CAMPAIGN’ command.

As I said this has worked fine for ages, however today we have tried to setup a Report Caster version that enquires on a Campaign. So the report builds a smaller version of ORDERS, but then fails with an error:

INVALID KEYWORD TABLENAME. ORDERS LN 2

We have done lots of testing today and it is definitely related to the fact that we name our HOLD file the same as one of our system files. This hasn't caused a problem before and WebFocus has always been able to recognise that when we refer to ORDERS it uses the HOLD file, if created.

Is there a reason that the Report Caster should have a problem with this?

These are AS400 DB2 files.

Thanks, Mark.

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

I don't know why ReportCaster would behave this way, but I do know I would never ever "name our HOLD file the same as one of our system files".

There's other ways to deal with this, like setting a Dialogue Manager variable that contains the name of the Orders file, whether it's the DB2 table "ORDERS" or the HOLD file "HORDERS" (for example).

In the main report code, I would add -DEFAULT &ORDERS_FILE = 'ORDERS';

Then if the special process is run I would add -SET &ORDERS_FILE = 'HORDERS';

I would then use the DM variable: TABLE FILE &ORDERS_FILE and everything else would remain the same.

Thanks Francis,

However would this work for joins?

A basic example of our standard report as is:

-*-----STANDARD PROCESSING

JOIN
PROD_NO IN ORDERS TO
PROD_NO IN PRODUCT AS J0
END

JOIN
CUST_NO IN ORDERS TO
CUST_NO IN CUSTOMER AS J1
END

TABLE FILE ORDERS
PRINT *
...ETC


Example of proposed Campaign Enquiry logic:

...
...
...
ON TABLE HOLD AS &ORDERS_FILE
END
-RUN

-*-----STANDARD PROCESSING

JOIN
PROD_NO IN &ORDERS_FILE TO
PROD_NO IN PRODUCT AS J0
END

JOIN
CUST_NO IN &ORDERS_FILE TO
CUST_NO IN CUSTOMER AS J1
END

TABLE FILE &ORDERS_FILE
PRINT *
...ETC

I am away from my PC to test this now, but would certainly be ideal if it could work!

Yes, this would work. The Dialogue Manager variable gets translated to the the value before the JOIN, TABLE, HOLD, or any other command gets executed.

As an aside, joining from a HOLD file to a RDBMS can be inefficient, depending on how many rows there are in the HOLD file. Joining to PRODUCT and CUSTOMER (if they're DB2 tables) ought to be done in the original ORDERS extract.

Cheers,