0

I have a Service table and a Payments table. I want my clients to be able to pay for one service with multiple payments, but when I try to create a form for making a payment, I'm unable to make a

"ServiceA"."Cost" - SUM("Payment"."Amount")

substraction in my SELECT query that would show the amount left to pay. I'm being told Strings cannot be added or subtracted in dialect 3 even though all the relevant fields are Numeric, with the same amount of decimal points and everything...

Any idea on what I might be doing wrong?

EDIT: More details. The relevant tables are actually as follows: ServiceA, ServiceB, ServiceC, ServiceD all have a numeric field Cost. Their primary keys are actually foreign keys - they are all tied to an auto_increment BIGINT field in a table For. This is for the purpose of giving them auto_incrementing, yet non-repeating IDs.

There's a table PS (stands for Payment-Service) functions as a intermediate table for a multi-multi relationship. It has two foreign keys - one from Payments and one from For. This gives me the option to have multiple payments for one service or to have one payment cover multiple services.

Additionally, every Service table has a field referencing the ID of the client from "Clients".

In my SELECT, I try to do the following:

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"

This is for a dropdown list in a Create Payment form. It seems the problematic parts are the ones in bold.

eworm
  • 27
  • 6
  • 1
    can I suggest you to show us the table structure for the relevant tables, the attempted query and the error you get? Even if you are told something you'd better try it first, no? – Lelio Faieta Oct 23 '18 at 12:27
  • Added more details to the question – eworm Oct 25 '18 at 07:58

1 Answers1

0

Try to change this

"ServiceA"."Cost" - SUM("Payment"."Amount")

to this

`ServiceA`.`Cost` - SUM(`Payment`.`Amount`)

Or cast it to a specific type

CAST("ServiceA"."Cost" AS DECIMAL(12,2)) - CAST(SUM("Payment"."Amount") AS DECIMAL(12,2));
Hendrik Prinsloo
  • 179
  • 10
  • No, Libre Office uses the " quotation marks for the names of database elements and the single ' ones for added strings if any. This is not the problem here. – eworm Oct 25 '18 at 07:25
  • Okay cool, you can try to cast it to a specific type like in the updated answer. – Hendrik Prinsloo Oct 25 '18 at 07:37
  • Didn't help. I've gone and changed the field types in all the relevant tables to DECIMAL (they were NUMERIC prior), but even then it still shows that "strings" can't be added or subtracted. – eworm Oct 25 '18 at 08:39