Generated column from another table's column-CodePudding

Transaction table:

ID	Name	Price	Amount Paid
1	Bruce Wayne	10.0	5.0
2	Lois Lane	33.33	22.22
3	Clark Kent	44.4	44.4
4	Bruce Wayne	15.0	20.0

Person table:

ID	Name	Total Price	Total Paid	Payment Due
1	Bruce Wayne	25.0	25.0	0.00
2	Lois Lane	33.33	22.22	11.11
3	Clark Kent	44.4	44.4	0.00

Transaction table is connected to application and Person table has ID and Name. PaymentDue column calculates TotalPrice - TotalPaid. Both TotalPrice and TotalPaid columns are empty and have default value of 0.00.

I want to generate/update TotalPrice and TotalPaid from Transaction table's Price and Amount Paid column where Transaction.Name = Person.Name.

I'm trying to create table with :

CREATE TABLE "Person" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "Name"  TEXT,
    "TotalPrice"    REAL AS (SUM("main.Transaction.Price") WHERE "main.Transaction.Name" = "Name"),
    "TotalPaid" REAL DEFAULT 0.00,
    "PaymentDue"    REAL NOT NULL AS ("TotalPrice" - "TotalPaid"),
    PRIMARY KEY("ID" AUTOINCREMENT)
);

It's giving an error:

Execution finished with errors. Result: near "WHERE": syntax error At line 1: CREATE TABLE "Person" ( "ID" INTEGER NOT NULL UNIQUE, "Name" TEXT, "TotalPrice" REAL AS (SUM("main.Transaction.Price") WHERE

I also tried :

"TotalPrice"    REAL AS (SELECT SUM("main.Transaction.Price") WHERE "main.Transaction.Name" = "Name"),

Error :

Execution finished with errors. Result: near "SELECT": syntax error At line 1: CREATE TABLE "Person" ( "ID" INTEGER NOT NULL UNIQUE, "Name" TEXT, "TotalPrice" REAL AS (SELECT

Also :

"TotalPrice"    REAL AS (SUM(t.Price) FROM Transaction t WHERE "main.Transaction.Name" = "Name"),

Error:

Near "FROM" syntax error

This app is to check customer/vendor payments and inventory management. I created Person table with :

CREATE TABLE "Person" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "Name"  TEXT,
    "TotalPrice"    REAL DEFAULT 0.00,
    "TotalPaid" REAL DEFAULT 0.00,
    "PaymentDue"    REAL NOT NULL AS ("TotalPrice" - "TotalPaid"),
    PRIMARY KEY("ID" AUTOINCREMENT)
);

And Transaction table :

CREATE TABLE "Transaction" (
    "ID"    INTEGER NOT NULL UNIQUE,
    "Name"  TEXT,
    "Price" REAL  NOT NULL DEFAULT 0.00,
    "AmountPaid"    REAL NOT NULL DEFAULT 0.00,
    PRIMARY KEY("ID" AUTOINCREMENT)
);

I can do update query but don't know how to use SUM().

CodePudding user response：

You are trying to create a generated column, but it is not possible because the expression refers to a column of another table (not to mention that it does aggregation on this column).

This constraint is documented in Generated Columns/Capabilities:

The expression of a generated column can refer to any of the other declared columns in the table, including other generated columns, as long as the expression does not directly or indirectly refer back to itself.

So, the expressions:

SUM("main.Transaction.Price") WHERE "main.Transaction.Name" = "Name"

and

SELECT SUM("main.Transaction.Price") WHERE "main.Transaction.Name" = "Name"

are not allowed.

What you should do, is create a View instead of a table:

CREATE VIEW view_Person AS
SELECT ROW_NUMBER() OVER (ORDER BY Name) ID,
       Name,
       SUM(Price) TotalPrice,
       SUM(AmountPaid) TotalPaid,
       SUM(Price) - SUM(AmountPaid) PaymentDue
FROM "Transaction"
GROUP BY Name;

and get the resultset that you need by selecting from the view:

SELECT * FROM view_Person;

See the demo.

CodePudding user response：

The expression of a generated column may only reference constant literals and columns within the same row, and may only use scalar deterministic functions. The expression may not use subqueries, aggregate functions, window functions, or table-valued functions.

Read the docs https://sqlite.org/gencol.html