我有一个Service
表和一个Payments
表。我希望我的客户能够通过多次付款为一项服务付款,但是当我尝试创建付款表单时,我无法进行付款
"ServiceA"."Cost" - SUM("Payment"."Amount")
substraction
在我的SELECT
查询中,将显示剩余的金额。有人告诉我Strings cannot be added or subtracted in dialect 3
即使所有相关字段都是数字,小数点数量相同,所有内容......
知道我可能做错了什么吗?
编辑: 更多细节。相关表格实际如下:ServiceA
、ServiceB
、ServiceC
、ServiceD
都有一个数值字段Cost
。它们的主键实际上是外键 - 它们都绑定到表For
中的auto_increment BIGINT
字段。这是为了给它们auto_incrementing
,但非重复的ID。
有一个表PS
(代表Payment-Service
(用作多对多关系的中间表。它有两个外键 - 一个来自Payments
,一个来自For
。这使我可以选择为一项服务进行多次付款,或者一次付款涵盖多项服务。
此外,每个Service
表都有一个字段,该字段引用"客户端"中的客户端 ID。
在我的SELECT
中,我尝试执行以下操作:
SELECT "Clients"."Name" || ': ' || "ServiceA"."Name" || ' for ' || "ServiceA"."Cost" - SUM("Payments"."Amount") AS "To-Pay", "ID-Name" AS "ID"
FROM "ServiceA"
LEFT OUTER JOIN "Clients" ON "ServiceA"."Client" = "Clients"."ID-Client"
INNER JOIN "For" ON "ServiceA"."Id-ServA" = "For"."ID"
LEFT OUTER JOIN "PS" ON "For"."ID" = "PS"."For"
LEFT OUTER JOIN "Payments" ON "PS"."Payment" = "Payments"."For"
GROUP BY "To-Pay, "ID"
HAVING "PS"."Payment" IS NULL OR **SUM("Payments"."Amount") < "ServiceA"."Cost"
UNION ALL
(like the above but with other "Service" tables)
ORDER BY "To-Pay"
这适用于Create Payment
窗体中的下拉列表。似乎有问题的部分是粗体部分。
尝试更改此设置
"ServiceA"."Cost" - SUM("Payment"."Amount")
对此
`ServiceA`.`Cost` - SUM(`Payment`.`Amount`)
或将其强制转换为特定类型
CAST("ServiceA"."Cost" AS DECIMAL(12,2)) - CAST(SUM("Payment"."Amount") AS DECIMAL(12,2));