Strange report bug 2

Several years ago, I wrote¹ 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