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.

Mark Rotteveel · Accepted Answer · 2021-03-05T08:37:08.717

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:

Alias the table in the subquery to disambiguate it from the table itself
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)
(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) )
);

I'm trying to add the date from T1 into the datediff line, with COALESCE. Basically, I have one date in T1 and multiple dates in T2 (when was T1 updated). I'm interested in getting the last date. `SELECT DATEDIFF (day, COALESCE (MAX(src.T2_DATE), T1_DATE), CURRENT_DATE) FROM T2 src, T2, T1 WHERE src.FK_T1 = T2.FK_T1 GROUP BY src.FK_T1` I'm receiving an error with the above; "Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)" — Dragos Dascalu, Mar 09 '21 at 10:26
That is because the expresson as shown isn't valid because both `day` and `t1_date` aren't part of your `group by` (but adding them to the `group by` is not the solution!). You will need to restructure your query in a way that produces the desired result. I recommend asking a new question. — Mark Rotteveel, Mar 09 '21 at 10:37

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

1 Answers1

Linked