在iReport中操作日期库(Manipulate the date base in iReport)

在iReport中操作日期库(Manipulate the date base in iReport)

希望有人能帮我理解如何解决我的问题。 我在我的数据库表PAYMENTS中有这样的：

| PAYMENTS.PAYMENT | PAYMENTS.TOTAL | | CASH |1000 | | DEBT |500 | | DEBTPAID |200 |

问题是： CASH计算为销售现金加DEBTPAID 。 所以我需要在我的报告中单独记录这些数据（ CASH-DEBTPAID AS CASH ）。

如上所述，我如何在iReport中修改PAYMENTS.PAYMENT 。 非常感谢您的支持！ 先谢谢你！

hope someone could help me to understand how to solve my question. I have in my datebase table PAYMENTS like this:

| PAYMENTS.PAYMENT | PAYMENTS.TOTAL | | CASH |1000 | | DEBT |500 | | DEBTPAID |200 |

The problem is: CASH is calculated as cash from sales plus DEBTPAID. So I need to have this data individualy (CASH-DEBTPAID AS CASH) in my report.

How can I do in iReport to modify PAYMENTS.PAYMENT as I describe above. I much appreciate your support! Thank you in advance!

最满意答案

这个SQL会为CASH和DEBT提供一个单独的记录：

SELECT CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END AS PAYMENT, SUM(PAYMENTS.TOTAL) AS TOTAL FROM PAYMENTS GROUP BY CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END

This SQL would give you a separate record for CASH and DEBT:

SELECT CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END AS PAYMENT, SUM(PAYMENTS.TOTAL) AS TOTAL FROM PAYMENTS GROUP BY CASE WHEN PAYMENTS.PAYMENT IN ('CASH', 'DEBTPAID') THEN 'CASH' ELSE PAYMENTS.PAYMENT END