Computed table column with MAX value between rows containing a shared value

Question

I have the following table

CREATE TABLE T2
( ID_T2 integer NOT NULL PRIMARY KEY,
  FK_T1 integer,                    <--- foreign key to T1(Table1)
  FK_DATE date,                     <--- foreign key to T1(Table1)
  T2_DATE date,                     <--- user input field
  T2_MAX_DIFF COMPUTED BY ( (SELECT DATEDIFF (day, MAX(T2_DATE), CURRENT_DATE) FROM T2 GROUP BY FK_T1) )
);

I want T2_MAX_DIFF to display the number of days since last input across all similar entries with a common FK_T1.

It does work, but if another FK_T1 values is added to the table, I'm getting an error about "multiple rows in singleton select".

I'm assuming that I need some sort of WHERE FK_T1 = FK_T1 of corresponding row. Is it possible to add this? I'm using Firebird 3.0.7 with flamerobin.

Answer 1

The error "multiple rows in singleton select" means that a query that should provide a single scalar value produced multiple rows. And that is not unexpected for a query with GROUP BY FK_T1, as it will produce a row per FK_T1 value.

To fix this, you need to use a correlated sub-query by doing the following:

1. Alias the table in the subquery to disambiguate it from the table itself
2. Add a where clause, making sure to use the aliased table (e.g. src, and src.FK_T1), and explicitly reference the table itself for the other side of the comparison (e.g. T2.FK_T1)
3. (optional) remove the GROUP BY clause because it is not necessary given the WHERE clause. However, leaving the GROUP BY in place may uncover certain types of errors.

The resulting subquery then becomes:

(SELECT DATEDIFF (day, MAX(src.T2_DATE), CURRENT_DATE) 
 FROM T2 src 
 WHERE src.FK_T1 = T2.FK_T1 
 GROUP BY src.FK_T1)

Notice the alias src for the table referenced in the subquery, the use of src.FK_T1 in the condition, and the explicit use of the table in T2.FK_T1 to reference the column of the current row of the table itself. If you'd use src.FK_T1 = FK_T1, it would compare with the FK_T1 column of src (as if you'd used src.FK_T1 = src.FK_T2), so that would always be true.

CREATE TABLE T2
( ID_T2 integer NOT NULL PRIMARY KEY,
  FK_T1 integer, 
  FK_DATE date,
  T2_DATE date,
  T2_MAX_DIFF COMPUTED BY ( (
    SELECT DATEDIFF (day, MAX(src.T2_DATE), CURRENT_DATE) 
    FROM T2 src 
    WHERE src.FK_T1 = T2.FK_T1 
    GROUP BY src.FK_T1) )
);