Several years ago, I
wrote1 about a bug in a report: although there was a join to a
specific table (STACK2), the report behaved as if this join was
non-existent. When I dumped the report in WINDBI, indeed the join to STACK2
had disappeared. The fix was to add to the report another field from STACK2
(there are only two fields in this table; STACK2.ELEMENT was used to join
with something, so obviously STACK2.TYPE had to be added).
Over the past few days, I've been helping another programmer write a
somewhat complicated report that is supposed to show open invoices and
payments for suppliers at a given date, where the prices have been converted
to dollars. One of the complications of this report is that it has to use
two instances of CURREGITEMS, one fixed to have its currency dollars (-2)
and its date set to the parametric conversion date. The other instance is
joined to the customer's currency: this allows for the conversion of a
currency other than NIS or $ to be converted.
Although the join ACCOUNTS.CURRENCY
= CURREGITEMS1.CURRENCY appears in the report, the report behaves
as if this join does not exist when the report is run. Again, dumping the
report shows that this join does not exist. I partially solved the problem
by having only ACCOUNTS.CURRENCY appear as a report field, then adding =
CURREGITEMS1.CURRENCY as its
expression (i.e. in a son form of the report field).
Why do I write 'partially'? Because when ACCOUNTS.CURRENCY is -1, i.e. NIS,
there is no
corresponding record in CURREGITEMS. Indeed, originally the join was a left
join to solve the shekel problem. But how could I fake a left join when I'm
using CURREGITEMS as an expression? My off-the-cuff solution was to use the
following expression
= (ACCOUNTS.CURRENCY = -1 ?
ACCOUNTS.CURRENCY : CURREGITEMS.CURRENCY1)
but I'm not totally convinced that this is the correct solution. Indeed, the
programmer that I was helping informed me that it worked, but that she had to
add the 'distinct' flag to the report.
The original problem (see first paragraph) was solved by adding another
field of STACK2. Here, other fields of CURREGITEMS1 are being used, but
they're not being displayed. There is a field in CURREGITEMS
called RREXCHANGE that appears to be zero all the time, so this field
could be displayed but have the 'don't display if zero' option set.
Internal links
[1] 37